Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtract day and time w/o weekends or holidays
Is there a way to subtract the day and time in two cells and not
include weekends and holidays. The weekends would start at 5:00 p.m. Friday and end at 8:00 am Monday. Holidays would start at 5:00 p.m. the day before the holiday and end on 8:00 am the day after the holiday. If the holiday falls on a Friday, the time that would be exluded would be from Thursday at 5:00 p.m. to 8:00 a.m. Monday. If the holiday falls on Monday, the time that would have to be exluded would be 5:00 p.m Friday to 8:00 a.m. Tuesday. What I am trying to do is below (no weekend example here) Cell A2 = 08/06/08 2:00 pm Cell A1 = 08/05/08 1:00 pm Cell A2 - Cell A1 = 23 hours Cell A2 = 08/01/2008 4:00 P.m. Cell A1 - 08/04/2008 9:30 a.m. Cell A2 - Cell A1 = 2.5 hours (Because this is a weekend.) The end result would be the number of hours or days with fractions of hours with weekends excluded. I am trying to use only working business days. I looked at the worksheet functions and couldn't figure out to nest them together. I think this may have to be a program in VBA and my skills don't come anywhere close to this. Right now I am doing it manually and it is taking forever. I am on row 45 of 832. I am hoping to not have to look up holidays and put them in manually. Maybe Excel could do this?? Thanks for any help!!! kajero View Public Profile Send a private message to kajero Find all posts by kajero Add kajero to Your Buddy List |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtract day and time w/o weekends or holidays
Give this a try:
=(NETWORKDAYS(A28+1,A29-1)+1-MOD(A28,1)+MOD(A29,1))-16/24 I used 16/24 at the end because I assumed two days over the weekend, with normal 8 hr. work day, per day, per 24 hr. period. Can someone else confirm? Regards, Ryan-- -- RyGuy "Trudy" wrote: Is there a way to subtract the day and time in two cells and not include weekends and holidays. The weekends would start at 5:00 p.m. Friday and end at 8:00 am Monday. Holidays would start at 5:00 p.m. the day before the holiday and end on 8:00 am the day after the holiday. If the holiday falls on a Friday, the time that would be exluded would be from Thursday at 5:00 p.m. to 8:00 a.m. Monday. If the holiday falls on Monday, the time that would have to be exluded would be 5:00 p.m Friday to 8:00 a.m. Tuesday. What I am trying to do is below (no weekend example here) Cell A2 = 08/06/08 2:00 pm Cell A1 = 08/05/08 1:00 pm Cell A2 - Cell A1 = 23 hours Cell A2 = 08/01/2008 4:00 P.m. Cell A1 - 08/04/2008 9:30 a.m. Cell A2 - Cell A1 = 2.5 hours (Because this is a weekend.) The end result would be the number of hours or days with fractions of hours with weekends excluded. I am trying to use only working business days. I looked at the worksheet functions and couldn't figure out to nest them together. I think this may have to be a program in VBA and my skills don't come anywhere close to this. Right now I am doing it manually and it is taking forever. I am on row 45 of 832. I am hoping to not have to look up holidays and put them in manually. Maybe Excel could do this?? Thanks for any help!!! kajero View Public Profile Send a private message to kajero Find all posts by kajero Add kajero to Your Buddy List |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtract day and time w/o weekends or holidays
After I posted this, A programmer wrote this formula for me and it
works beautifully once the Column and Rows are revised to match what I need. Assuming start time/date in A2 and end time/date in B2 and a list of holiday dates in F1:F10 (you can make this as big as you want but should be a single column) then you can try this formula in C2 =SUMPRODUCT((WEEKDAY(A2+ROW(INDIRECT("1:"&ROUND((B 2-A2)*1440,0)))/ 1440-1/2880+1-"17:00",2)<6)*(WEEKDAY(A2+ROW(INDIRECT("1:"&ROUND( (B2- A2)*1440,0)))/1440-1/2880-"08:00", 2)<6)*ISNA(MATCH(INT(A2+ROW(INDIRECT("1:"&ROUND((B 2-A2)*1440,0)))/ 1440-1/2880+1-"17:00"),F1:F10,0))*ISNA(MATCH(INT(A2+ROW(INDIRECT ("1:"&ROUND((B2- A2)*1440,0)))/1440-1/2880-"08:00"),F1:F10,0)))/1440 format C2 as [h]:mm Note: it's not the most "efficient" formula, it might calculate a little slowly, I suggest you try it on a limited number of rows initially The above formula won't work for ranges over approx 45 days |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date/Time Difference Excluding Weekends & Holidays | Excel Worksheet Functions | |||
Autofill weekends and holidays | Excel Worksheet Functions | |||
time interval between dates incl weekends excl holidays | Excel Discussion (Misc queries) | |||
Ignoring weekends & holidays-NETWORKDAYS? | Excel Worksheet Functions | |||
How to exclude weekends/holidays from plotted time series | Charts and Charting in Excel |