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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    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