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

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

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

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

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

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

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