Input Format for Text to Date computed column to output the correct time

[Sekayi_Liburd]

Hi guys! What is the correct input for a Text to Date computed column to output the correct time of an event?

I have tried inputting a math columns that use the following formulas:

1. YEAR(D)*10^4+MONTH(D)*10^2+DAY(D)

   

   But with this the Text to Date column gives all the events a time of 8:00 am

2. YEAR(D)*100000000 + MONTH(D)*1000000 + DAY(D)*10000 + HOUR(D)*100 + MINUTE(D)

   
   But with this the Text to Date column gives no output at all

Any ideas?

[Darren_Murphy]

It works for me when I include an offset which is the reverse of my actual offset. I am UTC+8, so I enter utc-8 and that gives me the correct result. Don’t ask me why

[Sekayi_Liburd]

hmm ok. I’ll try something similar. Thanks!

Which math date formula do you use for the input?

[Darren_Murphy]

Similar to yours, except without the Hours, Minutes & Seconds (because in my example I needed it anchored to midnight).

[Sekayi_Liburd]

mine still won’t accept the hours and minutes math version, it only accepts the shorter 8-digit input.

With the shorter version like what you are using, I can block-shift the time but it doesn’t pick up the individual times.

I would like it to show up like this…

instead of this

[Sekayi_Liburd]

I’ve seen advice on the forum that the math calculated dates are more reliable, that’s why I was hoping for a math output I can use in the Text to Date column that would honor the specific times for each item on the schedule.

[ThinhDinh]

@Sekayi_Liburd

mine still won’t accept the hours and minutes math version, it only accepts the shorter 8-digit input.

I think you have to specify the format yyyyLLddHHmmss.

But like Darren said, I have to use the timezone that is opposite to mine to get the right output. Seems like a bug.

[Sekayi_Liburd]

Oh perfect!! Thanks @ThinhDinh

When I used yyyyLLddHHmm to format it, it worked!! And I used utc+4 which is the inverse for my zone, hahaha.

Thanks to you both for your help