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!😁
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives