Date Helper Not Helping

Jacob Mizer

I created a driver check in and the shipper wonder if there was a way that they could see when a line last pick up. So I used the a date helper and a time helper to separate the information and I used a separate sheet to pull the information. The date helper formula i am using is:

=DATEONLY(Created@row) + IF(AND(VALUE(LEFT([Time Created]@row, FIND(":", [Time Created]@row) - 1)) <> 12, VALUE(LEFT([Time Created]@row, FIND(":", [Time Created]@row) - 1)) >= 5, FIND("P", [Time Created]@row) > 0), 1)

But you can see in the picture it not working that well. Any check in it puts the date two days ahead then after midnight it goes back to that date. What am I doing wrong?

Paul Newcome

My apologies.

=DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

Paul Newcome

Try this...

=DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(RIGHT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

Jacob Mizer

@Paul Newcome it comes up as #INVALID VALUE

Paul Newcome

My apologies.

=DATE(VALUE("20" + MID(Created@row, 7, 2)), VALUE(LEFT(Created@row, 2)), VALUE(MID(Created@row, 4, 2)))

Jacob Mizer

@Paul Newcome That worked!! Thank you!

Paul Newcome

Happy to help. 👍️