Home |
Search |
Today's Posts |
#1
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends.
Hi there
I would use this formula =IF(B1="sat",0,IF(B1="sun",0,7.5)) See if that works D "Phil B." wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends.
Does B1 actually contain the text "Sat", or is it a date formatted as ddd?
If the latter, change Shane's formula from =IF(OR(B1="Sat",B1="Sun"),0,7.5) to =IF(OR(TEXT(B1,"ddd")="Sat",TEXT(B1,"ddd")="Sun"), 0,7.5) -- David Biddulph "Phil B." wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends.
Shane's formula works if your DAYs are text values or date values formatted
using the TEXT function. If, instead, your DAYs are actual date values displayed using custom formats, then a formula like this should work: =IF(OR(WEEKDAY(B1)=1,WEEKDAY(B1)=7),0,7.5) This assumes that the first day is in cell B1. Hope this helps, Hutch "Phil B." wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends.
Hi,
As correctly stated by a number of responders, the formula will work IF your cells really contain Sat and Sun as mentioned in the original email. If the entries are actual dates then here is a really short formula: =IF(MOD(A1,7)1,7.5,0) -- Thanks, Shane Devenshire "Phil B." wrote: 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.
This one even shorter.
=(MOD(A1,7)1)*7.5 "ShaneDevenshire" wrote: Hi, As correctly stated by a number of responders, the formula will work IF your cells really contain Sat and Sun as mentioned in the original email. If the entries are actual dates then here is a really short formula: =IF(MOD(A1,7)1,7.5,0) -- Thanks, Shane Devenshire "Phil B." wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing scheduled work hours for weekends.
And the winner goes to Teethless mama!
It worked . . . THANKS to all! "Teethless mama" wrote: This one even shorter. =(MOD(A1,7)1)*7.5 "ShaneDevenshire" wrote: Hi, As correctly stated by a number of responders, the formula will work IF your cells really contain Sat and Sun as mentioned in the original email. If the entries are actual dates then here is a really short formula: =IF(MOD(A1,7)1,7.5,0) -- Thanks, Shane Devenshire "Phil B." wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing scheduled work hours for weekends | Excel Worksheet Functions | |||
how to find gaps in the hours scheduled to ensure shift coverage | Excel Worksheet Functions | |||
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) |