How to convert duration from hh:mm into decimal format?

Questions & Answers
,
1

Hello,

I’ve a timesheet and have there multiple time-entries of working time.

I have:

  • customernames
  • projectnames
  • dates
  • log times

Format: HH:mm → example: 01:30 for 1hour and 30 Minutes

What i want to achieve is:

  • group by customer
  • subgrouping by projectnames
  • sum of logtimes per projectname in decimal hours (like 15,8 hours for 15hours and 48 minutes) for previous week

What I already tried to sum and convert times:

  1. Connect to tool - works
  2. Aggregation by customername (Aggregat) - works
  3. Sub-Aggregation by Projects (Aggregat) - no glue, doesnt work in combination with aggretat befor
  4. Using numerica aggretor to sum and format Logtimes of Customer log times - don’t work, different errors like “Failed to map ‘value’: Function ‘formatDate’ finished with error! ‘01:45’ is not a valid date.”

I need some hints, how to go forward to achieve my goal.

Best regards,
Ayko

2

Hi @Ayko,
This is an interesting one…
I can think of a couple ways to handle it, both are equally complicated, but I’ll describe one.

Given an array of hh:mm durations:

  • Iterate the array twice
  • For the first iterator, aggregate only the hours. Sum the elements of this array to get total hours, for example 14 hours.
  • For the second iterator, aggregate only the minutes. Sum the elements of this array to get total minutes, for example 108 minutes.
  • Using the floor() function and mod operator, you can figure out the total hours and minutes in both decimal and h:m formats.

Please see this blueprint as an example.
hour and minute to decimal.json (13.1 KB)

2 Likes