Forum Discussion

NeilKloster's avatar
NeilKloster
Brass Contributor
Dec 28, 2022
Solved

calculate total hours between date/time (excluding weekends)

Hello,    I know that this is pretty easy for this group, but it's escaping me at the moment.  I want to calculate the hours between two fields, but I just want a simple number of hours.  No concat...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Dec 29, 2022

    NeilKloster  So there are 2 things happening here:

    a) any of the lines with start and end being the same day are 0 because you missed the last line of the equation

    b) the numbers look weird because you didn't format them to show [h]:mm

    If you want purely hours (i.e. [h]:mm of 1:30 would be 1.5) then just add 24* at the beginning (or *24 at the end) of the equation.

    see attached.

NeilKloster's avatar
NeilKloster
Brass Contributor
Dec 29, 2022

mtarler 

 

Okay, first and foremost - you are awesome!  The amount of effort you are putting into this to help me is amazing!  TYSM!!

 

So I put in that amazing formula and I'm getting results, but they are off.  I am including a simplified file example to show you what is going on.  Please ignore the conditional format coloring on column C. 

 

 

 

HansVogelaar's avatar
HansVogelaar
MVP
Dec 29, 2022

NeilKloster 

A shorter formula that works if neither Date/Time Opened not Date/Time Closed will be a weekend day:

 

=IF(OR(A2:B2=""),"",16*(NETWORKDAYS(A2,B2)-1)+24*(MOD(B2,1)-MOD(A2,1)))

  • NeilKloster's avatar
    NeilKloster
    Brass Contributor
    Dec 30, 2022
    Haha!! This is also works EXACTLY like the other one too - I just checked them both. Thank you so much!! Much appreciated everyone!

Resources