Prev: Divide by 0 Help
Next: In Excel, when i key in 02352 and press enter, the zero goes off
From: Johnny on 17 May 2010 09:07
I need a forumula to autimatically caculate the actual date that employees
are off for Christmas based on the year. If the holiday falls on a Saturday,
then Friday is the day off. If on a Sunday, then Monday is the day off.

For example, Chirstmas in 2010 falls on a Saturday. The foruma needs to
return 12/24/10 as the day off. If I change the year to 2011, the day off
would fall on 12/26/10.

Hopefully the forumual would translate for Thanksgiving, Independence Day
and New Year's day as well.

Thank you,



From: T. Valko on 17 May 2010 10:07
Try this...

A1 = some date

=A1+LOOKUP(WEEKDAY(A1,2),{1,6,7},{0,-1,1})

--
Biff
Microsoft Excel MVP


"Johnny" <john.herold(a)columbus.rr.com> wrote in message
news:4777D931-E3DF-4C10-9684-7AABD5899442(a)microsoft.com...
>I need a forumula to autimatically caculate the actual date that employees
> are off for Christmas based on the year. If the holiday falls on a
> Saturday,
> then Friday is the day off. If on a Sunday, then Monday is the day off.
>
> For example, Chirstmas in 2010 falls on a Saturday. The foruma needs to
> return 12/24/10 as the day off. If I change the year to 2011, the day off
> would fall on 12/26/10.
>
> Hopefully the forumual would translate for Thanksgiving, Independence Day
> and New Year's day as well.
>
> Thank you,
>
>
>


From: Luke M on 17 May 2010 10:56
While Biff's formula can easily be applied Independence Day and New Years,
Thanksgiving is a holiday that is always on the same day (but not date).

--
Best Regards,

Luke M
"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:Ot2Z9oc9KHA.5592(a)TK2MSFTNGP02.phx.gbl...
> Try this...
>
> A1 = some date
>
> =A1+LOOKUP(WEEKDAY(A1,2),{1,6,7},{0,-1,1})
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Johnny" <john.herold(a)columbus.rr.com> wrote in message
> news:4777D931-E3DF-4C10-9684-7AABD5899442(a)microsoft.com...
>>I need a forumula to autimatically caculate the actual date that employees
>> are off for Christmas based on the year. If the holiday falls on a
>> Saturday,
>> then Friday is the day off. If on a Sunday, then Monday is the day off.
>>
>> For example, Chirstmas in 2010 falls on a Saturday. The foruma needs to
>> return 12/24/10 as the day off. If I change the year to 2011, the day
>> off
>> would fall on 12/26/10.
>>
>> Hopefully the forumual would translate for Thanksgiving, Independence Day
>> and New Year's day as well.
>>
>> Thank you,
>>
>>
>>
>
>


 | 
Pages: 1
Prev: Divide by 0 Help
Next: In Excel, when i key in 02352 and press enter, the zero goes off