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
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
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:
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:
You can then use the column summary to insert a unit, let’s say: “days”
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
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
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.
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
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.