Skip to main content
Solved

Converting time diff formula output into Concatenate formula

  • February 3, 2021
  • 3 replies
  • 37 views
A

I am currently working on setting up a time sheet on one of my bases. I am having an issue with a formula to ID my entries.
I am trying to Concatenate the date, name of person, and total worked time all in one. I have the date and name figured out but I can’t get the total worked time to format in anything but seconds.

CONCATENATE(DATETIME_FORMAT({Start Time},‘l’), " - ", Who, " - ",{Total Hrs})

{Total Hrs} is currently this formula, DATETIME_DIFF({End Time},{Start Time}), which is fine because I can change the field to be a duration.

I have explored the community but haven’t been able to find a solution.
Thanks in advance for your help!

Best answer by Kamille_Parks11

If you want to turn seconds into hours you could use simple division (there are 3600 seconds in an hour). If you want to format as “# hours ## minutes”, do the following:

INT({Total Hrs} / 3600) & " hours " & INT(MOD({Total Hrs}, 3600) / 60) & " minutes"

3 replies

Kamille_Parks11
Forum|alt.badge.img+27

If you want to turn seconds into hours you could use simple division (there are 3600 seconds in an hour). If you want to format as “# hours ## minutes”, do the following:

INT({Total Hrs} / 3600) & " hours " & INT(MOD({Total Hrs}, 3600) / 60) & " minutes"
A
  • Adam_Marshall
  • Author
  • New Participant
  • February 3, 2021

If you want to turn seconds into hours you could use simple division (there are 3600 seconds in an hour). If you want to format as “# hours ## minutes”, do the following:

INT({Total Hrs} / 3600) & " hours " & INT(MOD({Total Hrs}, 3600) / 60) & " minutes"

Thanks so much!
I tweaked it a little bit to make it h:m but that worked perfectly!

I will need to dive into that code a little bit to further my knowledge for the future!
Is it basically converting the seconds into the largest round number and then the remainder into minutes?

Kamille_Parks11
Forum|alt.badge.img+27

Thanks so much!
I tweaked it a little bit to make it h:m but that worked perfectly!

I will need to dive into that code a little bit to further my knowledge for the future!
Is it basically converting the seconds into the largest round number and then the remainder into minutes?


That’s correct. INT() returns a whole number, so INT({Total Hrs} / 3600) gives you “how many complete hours”. MOD() gives you the remainder after dividing two numbers, so INT(MOD({Total Hrs}, 3600) / 60) gives you “how many minutes are left over”.

A
Terms & ConditionsAccessibility statement