Counting time based on dates formula

Platform discussions
,
1

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.

chrome_A1zehHRRLD
chrome_A1zehHRRLD1623×99 19.2 KB

IF(OR({Typ}="Nieobecność",{Typ}="Nadgodziny", {Typ}="Praca w dni wolne od pracy", {Typ}="Praca w nocy"),HOURS_DIFF(FORMAT_DATE({Data zakończenia},"HH:mm"), FORMAT_DATE({Data rozpoczęcia},"HH:mm")),"")
2

chrome_C2CIykzO7l
chrome_C2CIykzO7l1562×123 27.7 KB

I’m adding another screenshot.

3

Hi Karol,

I think the key here is what is your desired format?

For instance if you have a start datetime as Oct 3rd 10:00 and an end datetime as Oct 5th 22:00

Would you expect:

  1. 2.5 days
  2. 60 hours
  3. 2 days 12 hours 0 minutes
  4. 60:00
  5. something else?

This is really important. All are possible, but they will change the formula significantly. If you let me know, I’ll help you.

(just reformatting your formula now for practical reasons)

IF(
    OR(
        {Typ}="Nieobecność",
        {Typ}="Nadgodziny",
        {Typ}="Praca w dni wolne od pracy",
        {Typ}="Praca w nocy"
    ),
    HOURS_DIFF(
        FORMAT_DATE(
            {Data zakończenia},
            "HH:mm"
        ), 
        FORMAT_DATE(
            {Data rozpoczęcia},
            "HH:mm"
        )
    ),
    ""
)
1 Like
4

Hi Francis,

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.

chrome_qJd7i9qnn1
chrome_qJd7i9qnn11653×348 60 KB

5

Hi Karol,

So there are a few approaches here:

  1. 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.

Screenshot 2023-10-26 at 08.07.15
Screenshot 2023-10-26 at 08.07.151806×456 20.8 KB

Minutes Difference:

DAYS(
    {Data zakończenia},
    {Data rozpoczęcia}
)*24*60

Days:

MAX(
    ROUNDDOWN(
	    {Minutes Difference}/1440,
        0
    ),
    0
)

Hours:

ROUNDDOWN(
    MOD(
        {Minutes Difference}/60,
        24
    ),
    0
)

Minutes:

ROUNDDOWN(
    MOD(
        {Minutes Difference},
        60
    ),
    0
)

Formatted Duration:

CONCATENATE(
	IF({Days}<10,0,""),
    {Days},
    " ",
	IF({Hours}<10,0,""),
    {Hours},
    ":",
	IF({Minutes}<10,0,""),
    {Minutes}
)
  1. 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.

Hope that helps!

6

Hi Francis,

What should be added to the formula below to also take into account differences in days? I don’t want to separate the days column and the hours column.

IF(
OR(
{Typ}=“Nieobecność”,
{Typ}=“Nadgodziny”,
{Typ}=“Praca w dni wolne od pracy”,
{Typ}=“Praca w nocy”
),
HOURS_DIFF(
FORMAT_DATE(
{Data zakończenia},
“HH:mm”
),
FORMAT_DATE(
{Data rozpoczęcia},
“HH:mm”
)
),
“”
)

7

Hi Karol,

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.