Problem with trying to combine Date and Time fields. Any help please?

[#1]

Hello there,

I am trying to combine a date and time field together into one:

Job_Date	Job_Time
01/01/2015	00:11:23
04/01/2014	23:45:21
16/01/2015	10:21:33

I tried using:

timestamp#(Job_Date & ' ' & Job_Time,'DD/MM/YYYY hh:mm:ss') to get both of them in one field but this function seems to convert the date into numbers and keeps the time format strangely.

Any idea how I can get around this please?

[jonasheisterkam]

Hi,

if it is Date() and Time() you can use timestamp(Date+time). If you can not get it in time and date with the # functions use maketime/date.

[#3]

Hi Jonas,

Thanks for your reply. The original data is already in Date and Time format. Do you have a practical example of what to try with my two fields above?

[MarcoWedel · Accepted solution]

Instead of

timestamp#(Job_Date & ' ' & Job_Time,'DD/MM/YYYY hh:mm:ss')

try

timestamp(Job_Date+Job_Time,'DD/MM/YYYY hh:mm:ss')

or just

timestamp(Job_Date+Job_Time)

which uses your default timestamp Format.

Hole this helps

Regards

Marco

[rwunderlich]

You were close with your first attempt. The internal value for a datetime is an integer day since dec 31 1899 and time is represented as a fraction. Today at noon is 42027.5 

add in a  timestamp() formatting function to get the result you want.

timestamp(timestamp#(Job_Date & ' ' & Job_Time,'DD/MM/YYYY hh:mm:ss'))

[sunny_talwar]

Is this what you are trying to achieve? PFA

Best,

S

[#7]

Ah now I understand! Thank you so much Marco and Jonas!

[#8]

Yes thank you Sunindia!

[#9]

Hi guys,

I have one more problem

When I use a certain date field as part of time stamp it gives me the date and time from the date field.

When I add a time field to it, suddenly I get no results even though the time field is populated with values!

Any ideas?

[sunny_talwar]

The time field is showing up. Isn't this what you want?

Best,

S