Hi!!
I have a module with a value in minutes, I want to send it to GoogleSheet but already converted to HH:mm format.
I’ve tried this, but it sends me a meaningless time…
{{formatNumber(floor(34.time_in_minutes / 60); 2; “0”)}}}:{{formatNumber(floor(34.time_in_minutes % 60); 2; “0”)}}}
Thanks!
Welcome to the Make community!
The function formatNumber, does not perform zero left-padding of the number.
For more information, see the formatNumber function in the help center on how to use it.
samliew – request private consultation
Join the unofficial Make Discord server to chat with us!
1 Like
Hi @Creativia I was able to find a solution without using the formatNumber.
My set variable is a random number of minutes. You set your amount.
- I then get the number without rounding (since we always want to look down which is the issue with floor and round here) I do this by using substring to give me the first two values in our string of the minutes/60
- I then get the last two values of the string so the remaining value after dividing. I then multiply this number by 60 to get the number of minutes.
as you can see for 1002 minute it is 16.7 hours which is 16:42.
{{substring(16.minutes / 60; 0; 2)}}{{“:60” * ((substring(16.minutes / 60; 2; 4)))}}
If the minutes if between 1 and 1439 this formula may work:

This basically adds a number of minutes to unixtime then reformats it as just the HH:mm portion of the time. This will only work for durations of up to 23 hours and 59 minutes, or 1439 minutes.
For 1440 and beyond it gets more complicated…

This version figures out the number of days, multiplies by 24 to return hours, then concats that with “:” followed by the unixtime formatted as minutes.
If you combine the two, testing on the number of minutes, using the first formula for 1-1439 and the later for >1440, you get:

If you want to copy and paste it this might work:
{{if(19.value > 1439; round(19.value / 1440 * 24) + ":" + formatDate(addMinutes("1970-01-01"; 19.value); "mm"); formatDate(addMinutes("1970-01-01"; 19.value); "HH:mm"))}}
Here are some tests…
Input:
Outputs:
2 Likes