|
WORK HOURS DIFFERENCE BETWEEN TWO DATES
I need to calculate the total WORK-hours (08:00-17:00) between two
date/time-stamps; -excluding WEEKENDS -excluding PUBLIC HOLIDAYS eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 11-01-2008 11:00:00 A3 02:00 eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 14-01-2008 11:00:00 A3 11:00 eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 14-01-2008 09:00:00 A2 16-01-2008 11:00:00 A3 20:00 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
Hi,
Try this =(NETWORKDAYS(A1,A2)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1), B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1) Whe- A1= Earlier date/time A2= Later date/time B1 = 08:00 B2 = 17:00 Mike "CHRISTI" wrote: I need to calculate the total WORK-hours (08:00-17:00) between two date/time-stamps; -excluding WEEKENDS -excluding PUBLIC HOLIDAYS eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 11-01-2008 11:00:00 A3 02:00 eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 14-01-2008 11:00:00 A3 11:00 eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 14-01-2008 09:00:00 A2 16-01-2008 11:00:00 A3 20:00 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
Hello,
maybe http://www.sulprobil.com/html/count_hours.html can help you somewhat. It does not take holidays into account, though. Regards, Bernd |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
Hi,
Your other response made me aware that I'd missed publich holidays so change to this =(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1), B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1) Where C1 to C8 is your holiday list. Just a point to note is that I have assumed that a public holiday wouldn't be either of the dates in A1 or A2. If these dates were public holidays then the real start/end date is a day later/earlier and in any case I'm struggling to work it out otherwise. Perhaps someone can enlighten us. Mike "CHRISTI" wrote: I need to calculate the total WORK-hours (08:00-17:00) between two date/time-stamps; -excluding WEEKENDS -excluding PUBLIC HOLIDAYS eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 11-01-2008 11:00:00 A3 02:00 eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 14-01-2008 11:00:00 A3 11:00 eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 14-01-2008 09:00:00 A2 16-01-2008 11:00:00 A3 20:00 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
Mike - thank you this solved all; would never have figured this out by myself!
"Mike H" wrote: Hi, Your other response made me aware that I'd missed publich holidays so change to this =(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1), B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1) Where C1 to C8 is your holiday list. Just a point to note is that I have assumed that a public holiday wouldn't be either of the dates in A1 or A2. If these dates were public holidays then the real start/end date is a day later/earlier and in any case I'm struggling to work it out otherwise. Perhaps someone can enlighten us. Mike "CHRISTI" wrote: I need to calculate the total WORK-hours (08:00-17:00) between two date/time-stamps; -excluding WEEKENDS -excluding PUBLIC HOLIDAYS eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 11-01-2008 11:00:00 A3 02:00 eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 14-01-2008 11:00:00 A3 11:00 eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 14-01-2008 09:00:00 A2 16-01-2008 11:00:00 A3 20:00 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
your welcome
"CHRISTI" wrote: Mike - thank you this solved all; would never have figured this out by myself! "Mike H" wrote: Hi, Your other response made me aware that I'd missed publich holidays so change to this =(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1), B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1) Where C1 to C8 is your holiday list. Just a point to note is that I have assumed that a public holiday wouldn't be either of the dates in A1 or A2. If these dates were public holidays then the real start/end date is a day later/earlier and in any case I'm struggling to work it out otherwise. Perhaps someone can enlighten us. Mike "CHRISTI" wrote: I need to calculate the total WORK-hours (08:00-17:00) between two date/time-stamps; -excluding WEEKENDS -excluding PUBLIC HOLIDAYS eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 11-01-2008 11:00:00 A3 02:00 eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 14-01-2008 11:00:00 A3 11:00 eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 14-01-2008 09:00:00 A2 16-01-2008 11:00:00 A3 20:00 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
Mike -
I tried this, but had to change the cell references, but that did not work. Below is my example. Do you see what I'm doing wrong? Here is my formula: =(NETWORKDAYS(A2,B2)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D2,C2), D2)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),D2,C2) A2 = 1/16/2008 10:00 am B2 = 1/22/2008 9:34 am C2 = 08:00 am D2 = 17:00 pm My results are showing 1.48 Thanks, Kamp "Mike H" wrote: Hi, Try this =(NETWORKDAYS(A1,A2)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1), B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1) Whe- A1= Earlier date/time A2= Later date/time B1 = 08:00 B2 = 17:00 Mike "CHRISTI" wrote: I need to calculate the total WORK-hours (08:00-17:00) between two date/time-stamps; -excluding WEEKENDS -excluding PUBLIC HOLIDAYS eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 11-01-2008 11:00:00 A3 02:00 eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 14-01-2008 11:00:00 A3 11:00 eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 14-01-2008 09:00:00 A2 16-01-2008 11:00:00 A3 20:00 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
Hi Mike,
From many days I am working on this, but not able solve this issue. Thanks for your help... Regards, Rohit "Mike H" wrote: your welcome "CHRISTI" wrote: Mike - thank you this solved all; would never have figured this out by myself! "Mike H" wrote: Hi, Your other response made me aware that I'd missed publich holidays so change to this =(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1), B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1) Where C1 to C8 is your holiday list. Just a point to note is that I have assumed that a public holiday wouldn't be either of the dates in A1 or A2. If these dates were public holidays then the real start/end date is a day later/earlier and in any case I'm struggling to work it out otherwise. Perhaps someone can enlighten us. Mike "CHRISTI" wrote: I need to calculate the total WORK-hours (08:00-17:00) between two date/time-stamps; -excluding WEEKENDS -excluding PUBLIC HOLIDAYS eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 11-01-2008 11:00:00 A3 02:00 eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 14-01-2008 11:00:00 A3 11:00 eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 14-01-2008 09:00:00 A2 16-01-2008 11:00:00 A3 20:00 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
I have to thank you too because I was looking for this same answer!!! You're
awesome! ~Dee "Mike H" wrote: your welcome "CHRISTI" wrote: Mike - thank you this solved all; would never have figured this out by myself! "Mike H" wrote: Hi, Your other response made me aware that I'd missed publich holidays so change to this =(NETWORKDAYS(A1,A2,C1:C8)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1), B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1) Where C1 to C8 is your holiday list. Just a point to note is that I have assumed that a public holiday wouldn't be either of the dates in A1 or A2. If these dates were public holidays then the real start/end date is a day later/earlier and in any case I'm struggling to work it out otherwise. Perhaps someone can enlighten us. Mike "CHRISTI" wrote: I need to calculate the total WORK-hours (08:00-17:00) between two date/time-stamps; -excluding WEEKENDS -excluding PUBLIC HOLIDAYS eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 11-01-2008 11:00:00 A3 02:00 eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 11-01-2008 09:00:00 A2 14-01-2008 11:00:00 A3 11:00 eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm) A1 14-01-2008 09:00:00 A2 16-01-2008 11:00:00 A3 20:00 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
This formula calculates te total work hours and minutes between two dates.
I use it to calculate how long it takes for someone to answer my mails at work. One workday is 10 hours in this formula. You can change this. B3(mail send), format as d/mm/yyyy h:mm , 23/02/2012 09:00 G3(reply received), format as d/mm/yyyy h:mm , 28/02/2012 17:00 H3(hours and minutes, format as h:mm): =((((((NETWORKDAYS(B3;G3))-1)*10)+(IF(HOUR(B3)<HOUR(G3);HOUR(G3)-HOUR(B3);-(HOUR(B3)-HOUR(G3)))))*60)+(IF(MINUTE(B3)<MINUTE(G3);MINUTE( G3)-MINUTE(B3);(60-(MINUTE(B3)-MINUTE(G3)))-60)))/1440 |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
|
WORK HOURS DIFFERENCE BETWEEN TWO DATES
Assumption is Start time as 7:30 AM and End time as 5:30 PM for the consideration of Working HRS.
A1=Start Date and Time B1=End Date and Time The formula is: =IF((B1-A1)<=1,TEXT(MOD(A1,60),"hh:mm")-TEXT(0.3125,"hh:mm")+TEXT(0.729166666666667,"hh:mm ")-TEXT(MOD(B1,60),"hh:mm"),(((NETWORKDAYS(A1,B1,E1)+ 1)/24)*10)-(TEXT(MOD(A1,60),"hh:mm")-TEXT(0.3125,"hh:mm")+TEXT(0.729166666666667,"hh:mm ")-TEXT(MOD(B1,60),"hh:mm"))) |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
This formula also accounts for times that go past midnight (ie: 11pm to
7am)... =IF(AND(Start<"",Stop<""),ROUND(MOD(Stop-Start),1)*24,2),"") ...where 'Start' and 'Stop' are column-absolute,row-relative defined ranges with local scope. The cell remains empty until both Start/Stop have time values entered. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
" If these dates were public holidays then the real start/end date is a day
later/earlier and in any case I'm struggling to work it out otherwise". Mike's formula is great, but having issue when start date is a public holiday, does anyone know how to resolve this? Thank you. |
WORK HOURS DIFFERENCE BETWEEN TWO DATES
On Thursday, April 9, 2015 at 1:35:31 PM UTC+5:30, wrote:
" If these dates were public holidays then the real start/end date is a day later/earlier and in any case I'm struggling to work it out otherwise". Mike's formula is great, but having issue when start date is a public holiday, does anyone know how to resolve this? Thank you. =(NETWORKDAYS.INTL(B5,C5,11)-2)*(B2-B1)+(B2-MOD(B5,1))+(MOD(C5,1)-B1) use this formula to clear your query if you need to add office holidays then add Holiday list in networkdays formula |
| All times are GMT +1. The time now is 08:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com