Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to set Saturday as a working day
I want to use Excel to calculate leave. As Saturday is a working day, it is
not possible to use Networkdays and workdays function. Anyway to make Saturday as a working day? |
#2
|
|||
|
|||
If you want to just advance by 1 day, that is Sun and Mon are non-working
days, just take 1 from each day (including any holiday list) =networkdays(start_date-1,end_date-1) If you want to have one less day off per week, then use =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2) ={1,2,3,4,5, 6})) -SUMPRODUCT(--(COUNTIF(holidays,(ROW(INDIRECT(start_date&":"&end _date)))*(WE EKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)={1 ,2,3,4,5,6})))) -- HTH RP (remove nothere from the email address if mailing direct) "Chatter_tk" wrote in message ... I want to use Excel to calculate leave. As Saturday is a working day, it is not possible to use Networkdays and workdays function. Anyway to make Saturday as a working day? |
#3
|
|||
|
|||
Hi Bob,
Really thanks for your help. But I am rather new to excel and couldn't understand it fully. I have two column that contain the start_date and end_date. But when I replaced it using your solution, I got error. Do you have any other alternative? "Bob Phillips" wrote: If you want to just advance by 1 day, that is Sun and Mon are non-working days, just take 1 from each day (including any holiday list) =networkdays(start_date-1,end_date-1) If you want to have one less day off per week, then use =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2) ={1,2,3,4,5, 6})) -SUMPRODUCT(--(COUNTIF(holidays,(ROW(INDIRECT(start_date&":"&end _date)))*(WE EKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)={1 ,2,3,4,5,6})))) -- HTH RP (remove nothere from the email address if mailing direct) "Chatter_tk" wrote in message ... I want to use Excel to calculate leave. As Saturday is a working day, it is not possible to use Networkdays and workdays function. Anyway to make Saturday as a working day? |
#4
|
|||
|
|||
Which of the options that I offered applies here?
What are the columns, and what rows are used? -- HTH RP (remove nothere from the email address if mailing direct) "Chatter_tk" wrote in message ... Hi Bob, Really thanks for your help. But I am rather new to excel and couldn't understand it fully. I have two column that contain the start_date and end_date. But when I replaced it using your solution, I got error. Do you have any other alternative? "Bob Phillips" wrote: If you want to just advance by 1 day, that is Sun and Mon are non-working days, just take 1 from each day (including any holiday list) =networkdays(start_date-1,end_date-1) If you want to have one less day off per week, then use =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2) ={1,2,3,4,5, 6})) -SUMPRODUCT(--(COUNTIF(holidays,(ROW(INDIRECT(start_date&":"&end _date)))*( WE EKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)={1 ,2,3,4,5,6})))) -- HTH RP (remove nothere from the email address if mailing direct) "Chatter_tk" wrote in message ... I want to use Excel to calculate leave. As Saturday is a working day, it is not possible to use Networkdays and workdays function. Anyway to make Saturday as a working day? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working time and days | Excel Worksheet Functions | |||
Working time and days | Excel Discussion (Misc queries) | |||
Trace Dependents and Precedents not working | Excel Worksheet Functions | |||
Starting work period on a Saturday and ending on a friday | Excel Discussion (Misc queries) | |||
how do I adjust working calendar (6 day work week) in excel that . | Excel Worksheet Functions |