Has anyone used Bridge to bring Actual Hours against a project into Smartsheet?
I am bringing this in, however, having trouble translating over to hours after it comes in. It is in an odd time format for example ServiceNow shows Actual Hours 4940 Hours 47 Minutes and it comes in as 1970-07-25 20:47:59
Best Answer
-
Hi @Jenann
1970-07-25 20:47:59 to Hours 4940 Hours 47 Minutes conversion suggests the system uses unix time.
UNIX time is the number of seconds that have elapsed since January 1, 1970, 0:00:00 (UNIX epoch) in UTC time.
So, if the value comes in "1970-07-25 20:47:59", you can use the text functions such as MID() and get the year, month, day, hour, minute, and second value. Then, comparing it to the value of "1970-00-01 00:00:00", you will get the "Hours 4940 Hours 47 Minutes."
Example conversion formula:
=(DATE(Year@row, Month@row, Day@row) - DATE(1970, 1, 1)) * 24 + Hour@row
Answers
-
Hi @Jenann
1970-07-25 20:47:59 to Hours 4940 Hours 47 Minutes conversion suggests the system uses unix time.
UNIX time is the number of seconds that have elapsed since January 1, 1970, 0:00:00 (UNIX epoch) in UTC time.
So, if the value comes in "1970-07-25 20:47:59", you can use the text functions such as MID() and get the year, month, day, hour, minute, and second value. Then, comparing it to the value of "1970-00-01 00:00:00", you will get the "Hours 4940 Hours 47 Minutes."
Example conversion formula:
=(DATE(Year@row, Month@row, Day@row) - DATE(1970, 1, 1)) * 24 + Hour@row
-
This was so, so helpful!!! Provided exactly what I needed and solved a huge issue.... Thank you so much.
-
Happy to help!😁
