Formula for addition based on Day of the Week

  • Thread starter Thread starter Stacie
  • Start date Start date
S

Stacie

Hi everyone.

This is my first post - so please excuse me if it's too simplistic. All I
know is I need some help!

Column A - Dates 1/1/04 through 12/31/04
Column D - Cumulative total.

If the day in Column A is M - F, then 8 hours should be added to the
cumluative total for that day.
If the day in Column A is Sat or Sun, nothing added to the cumulative total.

Thanks in advance for your time and help,
Stacie
 
Hi Stacy!

Here's an easy to understand approach.

D1:
=IF(OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1),0,(1/24)*8)
Format > Custom Format [hh]:mm
D2:D366
=IF(OR(WEEKDAY(A2)=7,WEEKDAY(A2)=1),D1,D1+(1/24)*8)
Format > Custom Format [hh]:mm

The IF function uses an OR function as a condition. The requirement of
the condition is that it should return TRUE or FALSE and if you try:
=OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1)
You'll find that it returns FALSE for 1-Jan-2004 because the DOW was 5

The (1/24)*8 is used because time is recorded as a decimal part of a
day. 1/24 is 1 hour so (1/24)*8 is 8 hours.

The format [hh]:mm is used to stop the hours from "rolling over" to a
new day.

Now we could make this a lot more efficient but my aim here is to get
the basics understood first. Apologies if I'm teaching my
grandmother...

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Stacie,

Another way is us NETWORKDAYS().
In column D put =NETWORKDAYS("1/1/2004","12/31/2004")*8

RAC
 
Hi RAC!

This will fail if the Regional Settings are not the US standard of
mm/dd/yyyy.

=NETWORKDAYS("1-Jan-2004","31-Dec-2004")*8

Pedantic if Stacie is in US but crucial if practically anywhere else

Also I think that Stacie was after a running total and not just the
final answer. But NETWORKDAYS could be used for that using:

=NETWORKDAYS($A$1,A1)*8
for straight hours

=NETWORKDAYS($A$1,A1)*8/24
for hours in Time format
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top