S
S Davis
Hello all,
I was trying to reply to an older topic that I never got back to which
has since been shut, but I thought that this formula might be useful
for someone searching the archives.
This is in response to a person looking to calculate the amount of time
elapsed, during working hours, between dates. Specifically for
calculating the length of time to get back to a customer during regular
business days with a start and end date and time.Unfortunately it does
not account for weekends yet, but maybe if I need that I'll build it
into it later.
Starting date = B3
Ending date = F3
Beginning of regular work day (ie. 08:00) = Y2
Ending of regular work day (ie. 16:00) = Y3
Same day calculation = K3
I have a column (K) to tell whether or not the dates are within the
same day - if they are, display "Yes". Formula in K3 =
=IF(((F3-B3)*24)<=(($Y$3-$Y$2)*24),"Yes","")
Now the formula for calculating the working hours (as specified in
cells Y3 and Y2) between dates is:
=IF($K3="Yes",($F3-$B3),((ROUNDDOWN($B3,0)+$Y$3)-$B3)+((ROUNDDOWN($F3,0)-(ROUNDDOWN($B3,0)))-1)+(($F3-(ROUNDDOWN($F3,0)+$Y$2))))
This gives you a decimal figure for days. Leave as is for days, or
multiply by 24 for hours, or 1440 for minutes.
Hope it can help someone.
I was trying to reply to an older topic that I never got back to which
has since been shut, but I thought that this formula might be useful
for someone searching the archives.
This is in response to a person looking to calculate the amount of time
elapsed, during working hours, between dates. Specifically for
calculating the length of time to get back to a customer during regular
business days with a start and end date and time.Unfortunately it does
not account for weekends yet, but maybe if I need that I'll build it
into it later.
Starting date = B3
Ending date = F3
Beginning of regular work day (ie. 08:00) = Y2
Ending of regular work day (ie. 16:00) = Y3
Same day calculation = K3
I have a column (K) to tell whether or not the dates are within the
same day - if they are, display "Yes". Formula in K3 =
=IF(((F3-B3)*24)<=(($Y$3-$Y$2)*24),"Yes","")
Now the formula for calculating the working hours (as specified in
cells Y3 and Y2) between dates is:
=IF($K3="Yes",($F3-$B3),((ROUNDDOWN($B3,0)+$Y$3)-$B3)+((ROUNDDOWN($F3,0)-(ROUNDDOWN($B3,0)))-1)+(($F3-(ROUNDDOWN($F3,0)+$Y$2))))
This gives you a decimal figure for days. Leave as is for days, or
multiply by 24 for hours, or 1440 for minutes.
Hope it can help someone.