Calculate time in hours between two dates

[jlclyde]

I have see a lot of wonderful information on how to get work hours
between two dates. Everything from UDF to formulas that are as long
as my arm. What I need is a little confusing, at least to me. I need
to know the difference in hours between two dates, excluding weekends
and having Fridays be a shorter shift.

Monday 4:30-24:00
Tuesday 4:30-24:00
Wednesday 4:30-24:00
Thursday 4:30-24:00
Friday 6:30-12:30

I am hoping that someone has an idea,
Thank you in advance,
Jay

 

[Leo Rod]

Mr. Jay, check this formula and adapt it to your working schedule:

=IF((NOW()-(9/24)-A351)<=0,0,IF((NOW()-A351>17.5/24),8/24,IF((AND(0.375<(NOW()-A351),(NOW()-A351)<((12.5)/24))),((NOW()-A351)-9/24),IF(AND((13/24)<(NOW()-A351),(NOW()-A351)<((17.5)/24)),((NOW()-A351)-9.5/24),(3.5/24)))))

It gives "real time tracking" of the working hours everytime you recalculate
the spreadsheet; on it the marking time is:
9/24 = 9 am
17.5/24 = 5:30 pm
8/24 = 8 hours worked
9.5/24 = time between 17:30 and 24:00
A351 = Date

this formula is separated in 2 from 9 to 12 and from 12:30 to 5:30 -yes my
lunches are not paid...
And composed on a spreasheet that looks like this:
A B
Wednesday, August 01, 2007 8:00:00
Thursday, August 02, 2007 8:00:00
Friday, August 03, 2007 8:00:00
Monday, August 06, 2007 8:00:00
Tuesday, August 07, 2007 8:00:00
Wednesday, August 08, 2007 1:54:10


But having your time being constant from 4:30 (I'm guessing PM) to 24 this
is
(1-(16.5/24))*(number of monday to thursdays)+(1-(18/24))*(number of
fridays)
don't forget to change the cell format to time format to see the number of
hours.

Please let me know how this works for you.

Greetings
Leo.

 

[jlclyde]

Mr. Jay, check this formula and adapt it to your working schedule:

=IF((NOW()-(9/24)-A351)<=0,0,IF((NOW()-A351>17.5/24),8/24,IF((AND(0.375<(NO­W()-A351),(NOW()-A351)<((12.5)/24))),((NOW()-A351)-9/24),IF(AND((13/24)<(NO­W()-A351),(NOW()-A351)<((17.5)/24)),((NOW()-A351)-9.5/24),(3.5/24)))))

It gives "real time tracking" of the working hours everytime you recalculate
the spreadsheet; on it the marking time is:
9/24 = 9 am
17.5/24 = 5:30 pm
8/24 = 8 hours worked
9.5/24 = time between 17:30 and 24:00
A351 = Date

this formula is separated in 2 from 9 to 12 and from 12:30 to 5:30 -yes my
lunches are not paid...
And composed on a spreasheet that looks like this:
A B
Wednesday, August 01, 2007 8:00:00
Thursday, August 02, 2007 8:00:00
Friday, August 03, 2007 8:00:00
Monday, August 06, 2007 8:00:00
Tuesday, August 07, 2007 8:00:00
Wednesday, August 08, 2007 1:54:10

But having your time being constant from 4:30 (I'm guessing PM) to 24 this
is
(1-(16.5/24))*(number of monday to thursdays)+(1-(18/24))*(number of
fridays)
don't forget to change the cell format to time format to see the number of
hours.

Please let me know how this works for you.

Greetings
Leo.

- Show quoted text -

Leo,
I need to know the number of working hours between one date and
another excluding holidays and weekends. I know the weekday() formula
but how do you do the working hours?
Mondays 4:30 am to midnught
Tuesday same
Wednesday same
Thursday same
Friday 6:30 am to 12:30pm
A formula or a UDF woudl be awesome,
Jay

 

[Peo Sjoblom]

See if this helps

http://www.cpearson.com/excel/DateTimeWS.htm



--
Regards,

Peo Sjoblom

Mr. Jay, check this formula and adapt it to your working schedule:

=IF((NOW()-(9/24)-A351)<=0,0,IF((NOW()-A351>17.5/24),8/24,IF((AND(0.375<(NO­W()-A351),(NOW()-A351)<((12.5)/24))),((NOW()-A351)-9/24),IF(AND((13/24)<(NO­W()-A351),(NOW()-A351)<((17.5)/24)),((NOW()-A351)-9.5/24),(3.5/24)))))

It gives "real time tracking" of the working hours everytime you
recalculate
the spreadsheet; on it the marking time is:
9/24 = 9 am
17.5/24 = 5:30 pm
8/24 = 8 hours worked
9.5/24 = time between 17:30 and 24:00
A351 = Date

this formula is separated in 2 from 9 to 12 and from 12:30 to 5:30 -yes my
lunches are not paid...
And composed on a spreasheet that looks like this:
A B
Wednesday, August 01, 2007 8:00:00
Thursday, August 02, 2007 8:00:00
Friday, August 03, 2007 8:00:00
Monday, August 06, 2007 8:00:00
Tuesday, August 07, 2007 8:00:00
Wednesday, August 08, 2007 1:54:10

But having your time being constant from 4:30 (I'm guessing PM) to 24 this
is
(1-(16.5/24))*(number of monday to thursdays)+(1-(18/24))*(number of
fridays)
don't forget to change the cell format to time format to see the number of
hours.

Please let me know how this works for you.

Greetings
Leo.

- Show quoted text -

Leo,
I need to know the number of working hours between one date and
another excluding holidays and weekends. I know the weekday() formula
but how do you do the working hours?
Mondays 4:30 am to midnught
Tuesday same
Wednesday same
Thursday same
Friday 6:30 am to 12:30pm
A formula or a UDF woudl be awesome,
Jay

 

[jlclyde]

What CPearsons has done is to be able to calculate how many work hours
have transpired between dates. This only works if you have the same
number of hours each day. If the hours are different, like mine are,
this will nto work.

Someone?
Anyone?
Thanks,
Jay

 

[jlclyde]

I think I ahve part of it down now. Now I need to be able to subtract
13.5 hours if the two dates fall over a weekend. Pearsons formula
works and assumes that all days are 19.5 hours long. On Fridays
however we work 6 hours. So if a date falls over a weekend i need to
subtract 13.5 hours. For instance my dates are 8-6-07 04:30 to
8-13-07 10:00. Pearson's formula says there are 103.5 hours. It
shoudl be 90. I hoep this makes sense to someone. How do I get rid
of those 13.5 hours?

Thanks,
Jay

 

[Peo Sjoblom]

You could count how many Fridays there are and then subtract x*13.5

with start date in A1 and end date in B1

=SUMPRODUCT(INT((B1-WEEKDAY(A1+1-6)-A1+8)/ 7))

will count the Fridays and then just multiply it with 13.5 and subtract it
from the other formula


=(Other_Formula)-(SUMPRODUCT(INT((B1-WEEKDAY(A1+1-6)-A1+8)/ 7))*13.5)

 

[jlclyde]

Peo
This is exactly what I have been racking my brain to got
accomplished. Thank you for your help. Thsi will work for what I
need, but do you or anyone have any idea on how to turn this into a
UDF? So it woudl be putting this formula into a UDF.
=IF(AND(INT(A4)=INT(D4),NOT(ISNA(MATCH(INT(A4),HolidayList,0)))),
0,ABS(IF(INT(A4)=INT(D4),ROUND(24*(D4-A4),2),(24*(DayEnd-
DayStart)*(MAX(NETWORKDAYS(A4+1,D4-1,HolidayList),0)+INT(24*(((D4-
INT(D4))-(A4-INT(A4)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))
+MOD(ROUND(((24*(D4-INT(D4)))-24*DayStart)+(24*DayEnd-(24*(A4-
INT(A4)))),2),ROUND((24*(DayEnd-DayStart)),2))))))-(SUMPRODUCT(INT((D4-
WEEKDAY(A4+1-6)-A4+8)/ 7))*13.5)
Where A4 is start date and time (8-6-07 4:30) D4 is stop date and
time. Holiday list is a seperate list name as is DayStart and DayEnd.
Thanks,
Jay