How to convert hours to days in time tracking?

Hello,
I have a time tracking column which tracks the time spent on a task by hours, minutes and seconds.
I would like to calculate the number of days spent on the task.
I have tried multiple ways to make the formula in a separate formula column, but the result is not satisfactory.
The current formula iteration is

DIVIDE({PENDING WITH SALES#Hours}, 24)

But the value I receive in terms of days is nowhere near the expected results.
What am I doing wrong here?
Please help experts

@Tabish

Can you give more details on what results you are getting?

Hi @JCorrell
Basically, using the time tracking column, I am trying to understand how many days has been spent in finalizing a task.

The time tracking column tracks tasks by hour, minutes and seconds.
Ideally, if I where to divide the numbers of hours by 24, I should get the number of days.

However, when using the formula DIVIDE({PENDING WITH SALES#Hours}, 24), I am not getting proper results. The key here is the #Hours

Hope this would clarify things
Thanks for taking interest in this case

@Tabish, I get the concept. When I do it it works.

You say “I am not getting the proper results”. The question is what exactly are you getting?

Get some examples of the current time tracking fields and the formula result.

@Jcorrell
Thank you so much for your reply.
As an example below, the first column reflects the timer, the second column reflects the formula, which should be ideally in days, as you can see in the 2nd column, the formula doesn’t seem to work.

Hoe this helps
Once again, thank you so much for taking interest in this case

image image

@Tabish

There doesn’t appear to be any correlation between the time tracking column and the formula values. At this point, I would recommend contacting monday support regarding the issue.

@JCorrell
The 2nd column is based on the data received from the 1st column, as per the below formula

DIVIDE({PENDING WITH SALES#Hours}, 24)

@bradley @TRB-monday.com @Julia-monday.com @brett-monday.com @monday-team @Becca-monday.com @Oron-monday.com

Hi Team,
I am sorry for spamming everyone on this, but I need some help in writing a formula to calculate the number of days passed, which is linked to a timer column.

I tried multiple iterations of different formulas, but could not get the desired results.
Could you please advise on whom should I be speaking to for this please
Thanks a ton in advance, and once again, sorry for spamming everyone like this

Hi there!

Itai here from the monday.com support team. If I understand you correctly you want to convert time tracking (hh:mm) to days and hours?

If so, could you use this formula:

IF({Time Tracking#Hours}>=24,CONCATENATE(INT(DIVIDE({Time Tracking#Hours},24),0)&" d “&(INT({Time Tracking#Hours})-(24*ROUNDDOWN({Time Tracking#Hours}/24,0)))&” h “&MINUS(INT(MULTIPLY(DIVIDE({Time Tracking},3600),60),0),MULTIPLY(INT(DIVIDE({Time Tracking},3600),0),60))&” m"),CONCATENATE(INT({Time Tracking#Hours},0)&" h “&MINUS(INT(MULTIPLY(DIVIDE({Time Tracking},3600),60),0),MULTIPLY(INT(DIVIDE({Time Tracking},3600),0),60))&” m"))

It will look like this:
image

1 Like

Hi @itaishabtai
THank you so much for your reply.
This is monster formula!!!

I was wondering, if I wanted to only calculate the number of day, should the formula look something like this. Unfortunately, I am getting an error message

IF({PENDING WITH SALES#Hours}>=24,(INT(DIVIDE({PENDING WITH SALES#Hours},24),0)

Once again, thank you so much for your help

Hi @itaishabtai
Sorry, for the confusion, sorry I was not clear earlier.

My requirement is to convert the timer into days only. I do not require hours and minutes to be calculated.

As an example, if the time reads, 28 hours, then the timer should ideally show 1 day.
Hope this helps

Hi again @Tabish ,

Well in that case, we can use this formula:
ROUNDDOWN({Time Tracking#Hours}/24,0)

It will show you only the day number, but only if it’s higher than the 24 hours (this is why i used round down). So for 23h it will show 0 while for 24 and 25 it will show 1:
image

You can then use the column summary to insert a unit, let’s say: “days”
image

Makes sense?

Hi @itaishabtai
Thank you so much for your reply.
The formula works like a charm.

However, there is one peculiarity.
On timers which are running, the formula doesn’t run.
However, the timers which are paused, there is no problem with the formula.

For example, in the screenshot below the formula doesn’t work on the timers which are still running.
Is there a way around for this please?
Thank you so much for responding

image

hi @Tabish you’re correct! At the moment, the formula column will only read times that were stopped. While the clock is still running the time won’t be implemented/counted.

Sorry I can’t offer a workaround for this one :X

1 Like

Thanks @itaishabtai
No worries, thanks for your help.

I did a little testing…

It appears that when used in a formula, the value returned for a time tracking column is the value it was the last time it was stopped.

I looked at putting together a set of automations that would stop then restart time tracking once a day. I think it’s possible, but not very simple. The primary issue being NOT restarting if it was not running.

However, this would be very easy using an Integromat scenario.

1 Like

Thanks @JCorrell
I will look into making an automation, sounds doable.

Integromat…mmmmm, am not very well versed in that, I will look around
Thanks once again for the guidance

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.