| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated working days function
I have a start date in F2 and an end date in G2. I am currently using
this formula for the number of working days, which works fine: =SUM(INT((WEEKDAY(F2-{2,3,4,5,6})+G2-F2)/7)) What I need to do though is have the formula count the number of working days from F2 up until today(), but only IF G2 is greater than today, and still count the number of working days from F2:G2 if G2 is less than today. Also, if F2 is before 01/01/08, I need to have the formula calculate the days as if F2 = 01/01/08 Additionally, I need a formula to put in another column that will also count the number of working days from today() through G2, but only if G2 is greater than today() and also calculating F2 as starting 01/01/08 if F2 is before 01/01/08 Is this a possibility? Any help is greatly appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated working days function
Hi Chris
First we need a helper cell to hold todays date. I use A1 with this formula: =Today() Then we need helper column to calculate if F2 is greater or less 01-01-2008 - in cell J2 put this formula: =IF(F2<DATE(2008,1,1),DATE(2008,1,1),F2) I calculate working days in H2 (formula to be entered as one line): =IF(G2$A$1,SUM(INT((WEEKDAY(J2-{2,3,4,5,6})+$A$1-J2)/7)),SUM(INT((WEEKDAY(J2-{2,3,4,5,6})+G2-J2)/7))) In cell I2 enter formula below to calculate working days from today to date in G2: =IF(G2$A$1,SUM(INT((WEEKDAY($A$1-{2,3,4,5,6})+G2-$A$1)/7))) I think that is alŽl you need :-) Best regards, Per "Chris" skrev i meddelelsen ... I have a start date in F2 and an end date in G2. I am currently using this formula for the number of working days, which works fine: =SUM(INT((WEEKDAY(F2-{2,3,4,5,6})+G2-F2)/7)) What I need to do though is have the formula count the number of working days from F2 up until today(), but only IF G2 is greater than today, and still count the number of working days from F2:G2 if G2 is less than today. Also, if F2 is before 01/01/08, I need to have the formula calculate the days as if F2 = 01/01/08 Additionally, I need a formula to put in another column that will also count the number of working days from today() through G2, but only if G2 is greater than today() and also calculating F2 as starting 01/01/08 if F2 is before 01/01/08 Is this a possibility? Any help is greatly appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated working days function
=SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+MIN(G2,TODAY())-MAX(F2,DATE(2008,1,1)))/7))
and =MAX(0,SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+TODAY()-MAX(G2,DATE(2008,1,1)))/7))) but shouldn't you be using NETWORKDAYS? =NETWORKDAYS(MAX(F2,DATE(2008,1,1)),MIN(G2,TODAY() )) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Chris" wrote in message ... I have a start date in F2 and an end date in G2. I am currently using this formula for the number of working days, which works fine: =SUM(INT((WEEKDAY(F2-{2,3,4,5,6})+G2-F2)/7)) What I need to do though is have the formula count the number of working days from F2 up until today(), but only IF G2 is greater than today, and still count the number of working days from F2:G2 if G2 is less than today. Also, if F2 is before 01/01/08, I need to have the formula calculate the days as if F2 = 01/01/08 Additionally, I need a formula to put in another column that will also count the number of working days from today() through G2, but only if G2 is greater than today() and also calculating F2 as starting 01/01/08 if F2 is before 01/01/08 Is this a possibility? Any help is greatly appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated working days function
Hello,
See my formula for "count of working days" at http://www.sulprobil.com/html/date_formulas.html and set A1 to =MAX(F2,"01/01/2008") Regards, Bernd |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated working days function
Try this:
=NETWORKDAYS(MAX(F2,DATE(2008,1,1)),MIN(G2,TODAY() )) "Chris" wrote: I have a start date in F2 and an end date in G2. I am currently using this formula for the number of working days, which works fine: =SUM(INT((WEEKDAY(F2-{2,3,4,5,6})+G2-F2)/7)) What I need to do though is have the formula count the number of working days from F2 up until today(), but only IF G2 is greater than today, and still count the number of working days from F2:G2 if G2 is less than today. Also, if F2 is before 01/01/08, I need to have the formula calculate the days as if F2 = 01/01/08 Additionally, I need a formula to put in another column that will also count the number of working days from today() through G2, but only if G2 is greater than today() and also calculating F2 as starting 01/01/08 if F2 is before 01/01/08 Is this a possibility? Any help is greatly appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated working days function
On Apr 30, 1:42*am, "Bob Phillips" wrote:
=SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+MIN(G2,TODAY())-MAX(F*2,DATE(2008,1,1)))/7)) and =MAX(0,SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+TODAY()-MAX(G2,*DATE(2008,1,1)))/7))) but shouldn't you be using NETWORKDAYS? =NETWORKDAYS(MAX(F2,DATE(2008,1,1)),MIN(G2,TODAY() )) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Chris" wrote in message ... I have a start date in F2 and an end date in G2. I am currently using this formula for the number of working days, which works fine: =SUM(INT((WEEKDAY(F2-{2,3,4,5,6})+G2-F2)/7)) What I need to do though is have the formula count the number of working days from F2 up until today(), but only IF G2 is greater than today, and still count the number of working days from F2:G2 if G2 is less than today. Also, if F2 is before 01/01/08, I need to have the formula calculate the days as if F2 = 01/01/08 Additionally, I need a formula to put in another column that will also count the number of working days from today() through G2, but only if G2 is greater than today() and also calculating F2 as starting 01/01/08 if F2 is before 01/01/08 Is this a possibility? Any help is greatly appreciated.- Hide quoted text - - Show quoted text - Thanks Bob, I will try this. I am not using networkingdays becuase I don't know if everyone who will read this report has the toolpack. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated working days function
On Apr 30, 1:42*am, "Bob Phillips" wrote:
=SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+MIN(G2,TODAY())-MAX(F*2,DATE(2008,1,1)))/7)) and =MAX(0,SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+TODAY()-MAX(G2,*DATE(2008,1,1)))/7))) but shouldn't you be using NETWORKDAYS? =NETWORKDAYS(MAX(F2,DATE(2008,1,1)),MIN(G2,TODAY() )) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Chris" wrote in message ... I have a start date in F2 and an end date in G2. I am currently using this formula for the number of working days, which works fine: =SUM(INT((WEEKDAY(F2-{2,3,4,5,6})+G2-F2)/7)) What I need to do though is have the formula count the number of working days from F2 up until today(), but only IF G2 is greater than today, and still count the number of working days from F2:G2 if G2 is less than today. Also, if F2 is before 01/01/08, I need to have the formula calculate the days as if F2 = 01/01/08 Additionally, I need a formula to put in another column that will also count the number of working days from today() through G2, but only if G2 is greater than today() and also calculating F2 as starting 01/01/08 if F2 is before 01/01/08 Is this a possibility? Any help is greatly appreciated.- Hide quoted text - - Show quoted text - Bob - for some reason I am getting a #name? error on this formula |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated working days function
which particular one?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Chris" wrote in message ... On Apr 30, 1:42 am, "Bob Phillips" wrote: =SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+MIN(G2,TODAY())-MAX(F*2,DATE(2008,1,1)))/7)) and =MAX(0,SUM(INT((WEEKDAY(MAX(F2,DATE(2008,1,1))-{2,3,4,5,6})+TODAY()-MAX(G2,*DATE(2008,1,1)))/7))) but shouldn't you be using NETWORKDAYS? =NETWORKDAYS(MAX(F2,DATE(2008,1,1)),MIN(G2,TODAY() )) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Chris" wrote in message ... I have a start date in F2 and an end date in G2. I am currently using this formula for the number of working days, which works fine: =SUM(INT((WEEKDAY(F2-{2,3,4,5,6})+G2-F2)/7)) What I need to do though is have the formula count the number of working days from F2 up until today(), but only IF G2 is greater than today, and still count the number of working days from F2:G2 if G2 is less than today. Also, if F2 is before 01/01/08, I need to have the formula calculate the days as if F2 = 01/01/08 Additionally, I need a formula to put in another column that will also count the number of working days from today() through G2, but only if G2 is greater than today() and also calculating F2 as starting 01/01/08 if F2 is before 01/01/08 Is this a possibility? Any help is greatly appreciated.- Hide quoted text - - Show quoted text - Bob - for some reason I am getting a #name? error on this formula |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complicated working days calculation | Excel Worksheet Functions | |||
| Lookup function to take into account working days only | Excel Worksheet Functions | |||
| WOrkday function - change working days | Excel Worksheet Functions | |||
| Getting XL to Calc Working Days Lost without using NETWORKDAYS() Function | Excel Discussion (Misc queries) | |||
| Complicated formula not working in 97 | Excel Discussion (Misc queries) | |||
Linear Mode
