Has anyone used Bridge to bring Actual Hours against a project into Smartsheet?

Jenann

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 

jmyzk_cloudsmart_jp

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

https://app.smartsheet.com/b/publish?EQBCT=f6a381b2c27d45618510ae7dd1dd2a87

jmyzk_cloudsmart_jp

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

https://app.smartsheet.com/b/publish?EQBCT=f6a381b2c27d45618510ae7dd1dd2a87

Jenann

This was so, so helpful!!! Provided exactly what I needed and solved a huge issue.... Thank you so much.

jmyzk_cloudsmart_jp

Happy to help!😁