Making timestamp adds days to date

[dafnis14]

Hi,

based on other discussions, I used the following expression to create a timestamp

based on a date and an hour from different fields.

Timestamp(RsvLineCreateDate+ [RsvLine Create Hour], 'DD/MM/YY hh:mm')

But the final date in the timestamp is always 2 days later then the basis date.

for example:

date: 07/11/2011

time:07:51:00

final timestamp: 09/11/2011 07:51

Any idea why it happens?

Thanks!

[jonathandienst]

What (actual numeric) values do the two fields contain? It seems that one of them is not what you think.

[Kushal_Chawda]

can show the screenshot of RsvLineCreateDate,  [RsvLine Create Hour] in list box?

[dafnis14]

It seems that the issue is in the time field:

The expression =num([RsvLine Create Hour]) yields:

2.3270833333372

so the 2 days are actually hidden in the time field..

Displaying the time field as timestamp shows:

01/01/1900 07:51:00

How does this happen???

Thanks!

[Kushal_Chawda]

instead doing it using + operator, format it using Timestamp function like below

= Timestamp(Timestamp#(RsvLineCreateDate & [RsvLine Create Hour],'MM/DD/YYYYhh:ss'),'DD/MM/YYYY hh:mm')

[sasiparupudi1]

I think you need to convert the fields into date and time internal values before you add them.

ex:

= timestamp(date#('07/11/2011','DD/MM/YYYY') +time#('07:51:00','hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')

hth

Sasi

[dafnis14]

I thought it would be " healthier" to do the following:

Timestamp(Num((RsvLineCreateDate)+num(MakeTime(Hour([RsvLine Create Hour])))),'DD/MM/YYYY hh:00')

Looks messy but re solved the issue... 🙂

[dafnis14]

Thanks!

[MarcoWedel]

Hi,

another possibility to get rid of the (integer) day part of your time field:

Timestamp(RsvLineCreateDate + Frac([RsvLine Create Hour]), 'DD/MM/YY hh:mm')

hope this helps

regards

Marco