Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
use this to calculate the working days
=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5})-MIN(end_date,start_date)+8)/7)) or this if you want to exclude holidays =SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1-{2;3;4;5})-MIN(end_date,start_date)+8)/7)) -SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5},0))*(holidays=MIN(end_date,start_date))*(holi days<=MAX(end_date,start_date))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
A1: start date
A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) "Shelly" wrote: What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
I'm very new at this program so please bear with me...I did type the formula
in and it didn't work. I think I am just not putting the right information in the correct fields. The schedule is set up to have A1 to be the start date and B1 the finish date. We have multiple units being built at once. They take 10 days to complete, with a 4-day workweek. If you could walk me through it...that would be so great! And thank you for responding! "Teethless mama" wrote: A1: start date A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) "Shelly" wrote: What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
If you know the start date, and the end date and how long it takes, what
exactly are you trying to calculate? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... I'm very new at this program so please bear with me...I did type the formula in and it didn't work. I think I am just not putting the right information in the correct fields. The schedule is set up to have A1 to be the start date and B1 the finish date. We have multiple units being built at once. They take 10 days to complete, with a 4-day workweek. If you could walk me through it...that would be so great! And thank you for responding! "Teethless mama" wrote: A1: start date A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) "Shelly" wrote: What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
The end date is what I am trying to get...using a 4 day workweek.
"Bob Phillips" wrote: If you know the start date, and the end date and how long it takes, what exactly are you trying to calculate? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... I'm very new at this program so please bear with me...I did type the formula in and it didn't work. I think I am just not putting the right information in the correct fields. The schedule is set up to have A1 to be the start date and B1 the finish date. We have multiple units being built at once. They take 10 days to complete, with a 4-day workweek. If you could walk me through it...that would be so great! And thank you for responding! "Teethless mama" wrote: A1: start date A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) "Shelly" wrote: What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))))={ 1,2,3,4}),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(da ys))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... The end date is what I am trying to get...using a 4 day workweek. "Bob Phillips" wrote: If you know the start date, and the end date and how long it takes, what exactly are you trying to calculate? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... I'm very new at this program so please bear with me...I did type the formula in and it didn't work. I think I am just not putting the right information in the correct fields. The schedule is set up to have A1 to be the start date and B1 the finish date. We have multiple units being built at once. They take 10 days to complete, with a 4-day workweek. If you could walk me through it...that would be so great! And thank you for responding! "Teethless mama" wrote: A1: start date A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) "Shelly" wrote: What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
Where do I put this information?
"Bob Phillips" wrote: =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))))={ 1,2,3,4}),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(da ys)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... The end date is what I am trying to get...using a 4 day workweek. "Bob Phillips" wrote: If you know the start date, and the end date and how long it takes, what exactly are you trying to calculate? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... I'm very new at this program so please bear with me...I did type the formula in and it didn't work. I think I am just not putting the right information in the correct fields. The schedule is set up to have A1 to be the start date and B1 the finish date. We have multiple units being built at once. They take 10 days to complete, with a 4-day workweek. If you could walk me through it...that would be so great! And thank you for responding! "Teethless mama" wrote: A1: start date A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) "Shelly" wrote: What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
Anywhere. You just change the variable names to the appropriate cells.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... Where do I put this information? "Bob Phillips" wrote: =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))))={ 1,2,3,4}),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(da ys)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... The end date is what I am trying to get...using a 4 day workweek. "Bob Phillips" wrote: If you know the start date, and the end date and how long it takes, what exactly are you trying to calculate? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... I'm very new at this program so please bear with me...I did type the formula in and it didn't work. I think I am just not putting the right information in the correct fields. The schedule is set up to have A1 to be the start date and B1 the finish date. We have multiple units being built at once. They take 10 days to complete, with a 4-day workweek. If you could walk me through it...that would be so great! And thank you for responding! "Teethless mama" wrote: A1: start date A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) "Shelly" wrote: What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
4 day work week
Thank you for your time Bob! "Bob Phillips" wrote: Anywhere. You just change the variable names to the appropriate cells. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... Where do I put this information? "Bob Phillips" wrote: =start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))))={ 1,2,3,4}),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(da ys)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... The end date is what I am trying to get...using a 4 day workweek. "Bob Phillips" wrote: If you know the start date, and the end date and how long it takes, what exactly are you trying to calculate? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Shelly" wrote in message ... I'm very new at this program so please bear with me...I did type the formula in and it didn't work. I think I am just not putting the right information in the correct fields. The schedule is set up to have A1 to be the start date and B1 the finish date. We have multiple units being built at once. They take 10 days to complete, with a 4-day workweek. If you could walk me through it...that would be so great! And thank you for responding! "Teethless mama" wrote: A1: start date A2: end date =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<5)) "Shelly" wrote: What I need to do is have a project start date and an end date and a duration of days. We work a 4 day work week here and I need to not count Friday (Sat or Sun) in the duration of the projects. I just need a correct end date for the projects. Thanks so much!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
6 day work week | Excel Discussion (Misc queries) | |||
6 Day Work Week? | Excel Discussion (Misc queries) | |||
6 day work week | Charts and Charting in Excel | |||
How do I set up a 6 day work week in Excel? | Excel Worksheet Functions | |||
how to set a 6-day work week ? | Excel Worksheet Functions |