C
charlie brown
Hi
I am running a large spreadsheet that calculates the working hours of staff.
To calculate running totals, I need to differentiate between weekday workers
and shift workers. The sheet worked fine until the date tripped over from
2003 to 2004, a lot of the cells have turned into #NAME?
Xmas day and Boxing day fall on Sat/Sun in 2004, so weekday workers are
entitled to a public holiday on the Mon/Tues. I am using formulae
=IF(OR(AJ11=1,AJ11=7),WORKDAY(AI11,1,AE12),AI11)
=IF(OR(AJ12=1,AJ12=7),WORKDAY(AI12,1,AI12:AI13),AI12)
to shift the public holidays to the Mon & Tues, where AJ11 and AJ12 are days
of the week AI11:AI13 is the holiday list. The above formulae are in cells
AE11 and AE12.
I tested these formulae some time ago and I believed them to work OK at the
time. Since then I handed the sheet over to someone else who has
incorporated my work into the present system.
My question is......... are my formulae correct or is there a macro running
from another file causing the damage? Would the formulae work in all cases,
that is, if 25 December fell on a Sunday and therefore only need to shift
one of the public holidays to a weekday?
TIA
Charlie
I am running a large spreadsheet that calculates the working hours of staff.
To calculate running totals, I need to differentiate between weekday workers
and shift workers. The sheet worked fine until the date tripped over from
2003 to 2004, a lot of the cells have turned into #NAME?
Xmas day and Boxing day fall on Sat/Sun in 2004, so weekday workers are
entitled to a public holiday on the Mon/Tues. I am using formulae
=IF(OR(AJ11=1,AJ11=7),WORKDAY(AI11,1,AE12),AI11)
=IF(OR(AJ12=1,AJ12=7),WORKDAY(AI12,1,AI12:AI13),AI12)
to shift the public holidays to the Mon & Tues, where AJ11 and AJ12 are days
of the week AI11:AI13 is the holiday list. The above formulae are in cells
AE11 and AE12.
I tested these formulae some time ago and I believed them to work OK at the
time. Since then I handed the sheet over to someone else who has
incorporated my work into the present system.
My question is......... are my formulae correct or is there a macro running
from another file causing the damage? Would the formulae work in all cases,
that is, if 25 December fell on a Sunday and therefore only need to shift
one of the public holidays to a weekday?
TIA
Charlie