DateTimeAdd function keeps nulling out

[khuebsch5]

Hi all,

I've got a twofold issue:

1) I need to convert my timestamps to 24 hours (so basically converting PM to +12). However, everything I try with datetimeadd() nulls out my data, and for the life of me, I can't figure out why. I've tried even simply adding 12 hours to the field, and it still comes back null:

Any ideas on what's going on here?

2) This is the frustrating part -- all I need to do is subtract one timestamp from another, to see how big the gap is between the two numbers. Unfortunately, one of the times (the shift_start_time referenced above) is just HH:mm AM/PM, and one of the times is full on DD/MM/YYYY HH:mm:SS AM/PM.

Since I can't get the datetimeadd function to work, I'm not sure how else to directly compare the two fields. Does anyone have any suggestions?

Thanks!

[gabrielvilella]

You need first to convert to date time format using DateTimeParse([Date],'%d/%m/%Y %I:%M:%S %P'), add the missing date to the time of the other field and then calculate the difference. See attached.

[khuebsch5]

My issue seems to be that DateTime formulas don't seem to work with the value (coming out of Google Sheets), no matter what I do:

I even try using a DateConvert step first, which seems to work, but even then DateTimeParse seems to break down

[gabrielvilella · Accepted solution]

DateTime functions only work when you have date and time together. You need to concatenate them if they are separated, same way I did on my workflow on the second formula.

[khuebsch5]

OK, that solves the first half (the field missing a day). The second half is that the other field does have a date attached already, but I can't get it set to the same format as the previous field. Here's the current format, and I'm struggling to get it to match the output.

[clmc9601]

Hi @khuebsch5, that looks like a field type issue! I'd create a new column for the formatted [First_Punch_In] data instead of trying to modify the old one. The column containing this newly formatted data should be a string type, not a DateTime type.

[binuacs]

@khuebsch5 your given example the date format is different, it is mm/dd/yyyy in that case you need use the below formula

[gabrielvilella]

@khuebsch5 when posting on the community, please always post a sample dataset of the input and desired output. You'd have got the correct answer on the first reply.