For a long time I have been struggling with a formula that counts hours based on 2 columns with dates. I’ve gotten to the point where I don’t take into account dates, just hours. Do you know any proven way to display the differences in HH:MM also taking into account the date? Dateif unfortunately only shows minutes.
Thanks for the help!
After thinking about it for a while, I would like the “Wartość nadgodzin/nieobecności” column to be in the format “DD HH:MM” (E.g. 2[days] 10:30 [hh:mm), but to also take into account differences in days.
Currently, my formula if “start datetime as Oct 3rd 10:00 and an end datetime as Oct 5th 22:00”, will return the value 12:00 - does not take into account the difference in days.
Additionally, if the dates are e.g. start date Oct 5th 22:00, end date Oct 6th 00:30, the formula will return the value “-21:30” and it should be 2:30.
I would be able to do this in Excel so that it would count correctly, but unfortunately I have a big problem here.
To get exactly what you describe, I’d recommend breaking this down into columns. Unfortunately, monday.com does not make formatting easy, so maintaining and structuring a single formula would be really convoluted. There may be an easier way of doing this, but I can’t think of or find one right now.
Here’s a screenshot and the formulas for each column. Formatted Duration has what you are ultimately looking for and you can easily include the final value in your above formula. Beware that this returns an error if a date is missing, so it might cause you problems if you want to conditionally display some other data for instance.
Also, this will not work at all well if your End Time is before your Start Time.
Another approach could be to just use the DAYS() function. It gives you a time in days with a decimal. You can easily multiply this by 24 to get an hours difference. It’s not a natural format, however this will be far more useful for things like creating charts. You might want to have this in addition to your formatted duration.
The separated columns are not data—they are just formula columns. So once you have created them, you can just hide them from the view. You do not need to update them manually. They are there as helpers to format the data in the way that you have requested.
The simpler approach could be to just use the “DAYS()” function as I outlined in point (2).
Please feel free to reach out if you want some help getting this configured.