Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends
I have set up an worksheet for employees that automatically populates two
rows, the day (eg. Wed) the date (e.g. 15). The first day and date are calculated from a mm/dd/yy entered by the employee. Successive day and dates are completed by simply adding 1 to the previous cell formula. What formula do I use to automatically populate a third row with a sheduled value of "7.5" for Monday - Friday and "0.0" for Saturday and Sunday"? e.g. DAY: Wed Thu Fri Sat Sun Mon Tue etc. DATE: 15 16 17 18 19 20 21 etc. SCHE: 7.5 7.5 7.5 0.0 0.0 7.5 7.5 etc. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends
Hi Phil B,
considering your 3rd row is 3 and week days are in text format you can use following formula. =IF(OR(A1="sat",A1="sun"),0,7.5) and copy horizontally. Harshawardhan shastri ================================================== ====== "Phil B." wrote: I have set up an worksheet for employees that automatically populates two rows, the day (eg. Wed) the date (e.g. 15). The first day and date are calculated from a mm/dd/yy entered by the employee. Successive day and dates are completed by simply adding 1 to the previous cell formula. What formula do I use to automatically populate a third row with a sheduled value of "7.5" for Monday - Friday and "0.0" for Saturday and Sunday"? e.g. DAY: Wed Thu Fri Sat Sun Mon Tue etc. DATE: 15 16 17 18 19 20 21 etc. SCHE: 7.5 7.5 7.5 0.0 0.0 7.5 7.5 etc. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends
Hi,
=IF(OR(B1="Sat",B1="Sun"),0,7.5) This assumes that the first day is in cell B1. -- Thanks, Shane Devenshire "Phil B." wrote: I have set up an worksheet for employees that automatically populates two rows, the day (eg. Wed) the date (e.g. 15). The first day and date are calculated from a mm/dd/yy entered by the employee. Successive day and dates are completed by simply adding 1 to the previous cell formula. What formula do I use to automatically populate a third row with a sheduled value of "7.5" for Monday - Friday and "0.0" for Saturday and Sunday"? e.g. DAY: Wed Thu Fri Sat Sun Mon Tue etc. DATE: 15 16 17 18 19 20 21 etc. SCHE: 7.5 7.5 7.5 0.0 0.0 7.5 7.5 etc. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends
Hi Phil
=IF(WEEKDAY(B2,2)5,0.0,7,5) -- Regards Roger Govier "Phil B." wrote in message ... I have set up an worksheet for employees that automatically populates two rows, the day (eg. Wed) the date (e.g. 15). The first day and date are calculated from a mm/dd/yy entered by the employee. Successive day and dates are completed by simply adding 1 to the previous cell formula. What formula do I use to automatically populate a third row with a sheduled value of "7.5" for Monday - Friday and "0.0" for Saturday and Sunday"? e.g. DAY: Wed Thu Fri Sat Sun Mon Tue etc. DATE: 15 16 17 18 19 20 21 etc. SCHE: 7.5 7.5 7.5 0.0 0.0 7.5 7.5 etc. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends
Unfortunately, this function comes back with a 7.5 or FALSE for both Sat and
Sun. "ShaneDevenshire" wrote: Hi, =IF(OR(B1="Sat",B1="Sun"),0,7.5) This assumes that the first day is in cell B1. -- Thanks, Shane Devenshire "Phil B." wrote: I have set up an worksheet for employees that automatically populates two rows, the day (eg. Wed) the date (e.g. 15). The first day and date are calculated from a mm/dd/yy entered by the employee. Successive day and dates are completed by simply adding 1 to the previous cell formula. What formula do I use to automatically populate a third row with a sheduled value of "7.5" for Monday - Friday and "0.0" for Saturday and Sunday"? e.g. DAY: Wed Thu Fri Sat Sun Mon Tue etc. DATE: 15 16 17 18 19 20 21 etc. SCHE: 7.5 7.5 7.5 0.0 0.0 7.5 7.5 etc. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends
The response to this function is 0.0 for all days.
"Roger Govier" wrote: Hi Phil =IF(WEEKDAY(B2,2)5,0.0,7,5) -- Regards Roger Govier "Phil B." wrote in message ... I have set up an worksheet for employees that automatically populates two rows, the day (eg. Wed) the date (e.g. 15). The first day and date are calculated from a mm/dd/yy entered by the employee. Successive day and dates are completed by simply adding 1 to the previous cell formula. What formula do I use to automatically populate a third row with a sheduled value of "7.5" for Monday - Friday and "0.0" for Saturday and Sunday"? e.g. DAY: Wed Thu Fri Sat Sun Mon Tue etc. DATE: 15 16 17 18 19 20 21 etc. SCHE: 7.5 7.5 7.5 0.0 0.0 7.5 7.5 etc. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends
Hi Phil
Which row contains the Excel dates? I assumed it was row 2. If it is row 1, then just change to =IF(WEEKDAY(B1,2)5,0.0,7,5) -- Regards Roger Govier "Phil B." wrote in message ... The response to this function is 0.0 for all days. "Roger Govier" wrote: Hi Phil =IF(WEEKDAY(B2,2)5,0.0,7,5) -- Regards Roger Govier "Phil B." wrote in message ... I have set up an worksheet for employees that automatically populates two rows, the day (eg. Wed) the date (e.g. 15). The first day and date are calculated from a mm/dd/yy entered by the employee. Successive day and dates are completed by simply adding 1 to the previous cell formula. What formula do I use to automatically populate a third row with a sheduled value of "7.5" for Monday - Friday and "0.0" for Saturday and Sunday"? e.g. DAY: Wed Thu Fri Sat Sun Mon Tue etc. DATE: 15 16 17 18 19 20 21 etc. SCHE: 7.5 7.5 7.5 0.0 0.0 7.5 7.5 etc. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find gaps in the hours scheduled to ensure shift coverage | Excel Worksheet Functions | |||
get hours between 2 dates subtracting the weekends | Excel Discussion (Misc queries) | |||
scheduled hours total | Excel Discussion (Misc queries) | |||
scheduled hours total | Excel Worksheet Functions | |||
equation that adds hours as scheduled. 11:00 to 5:00 +6hrs. res. | Excel Discussion (Misc queries) |