Weekdays

K

Kerryn

I have a date in the format dd-mmm-yy but I want a column next to it that
says ALERT if the date is a Saturday or Sunday. What formula could I use?
 
P

Pete_UK

Assumig your date is in A1, use this:

=IF(WEEKDAY(A1,2)>5,"ALERT","")

and copy down.

If you didn't want to use up another column, you could apply
conditional formatting to each cell so that it changed colour for
weekend dates.

Hope this helps.

Pete
 
M

Marcelo

Hello

assuming your data starts on C7 you could use:

=IF(OR(WEEKDAY(C7)={1,7}),"alert","")

copy it down

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerryn" escreveu:
 
F

FSt1

hi
this formula might work for you.
assuming dates are in column A.......
=IF(OR(WEEKDAY(A2,1)=1,WEEKDAY(A2,1)=7),"Alert","")

regards
FSt1
 
H

houghi

Pete_UK said:
Assumig your date is in A1, use this:

=IF(WEEKDAY(A1,2)>5,"ALERT","")

and copy down.

If you didn't want to use up another column, you could apply
conditional formatting to each cell so that it changed colour for
weekend dates.
Click to expand...

Or instead of the alert, have the date changed to a monday. However then
you still would have holidays to account for.
This could be interesting if you want to take action on that specific
day.

houghi
 
P

Pete_UK

Assume your dates are in column A, starting with cell A2. Highlight
all the cells, with A2 the active cell. Click on Format | Conditional
Formatting, and in the first box that pops up choose Formula Is rather
than Cell Value Is. In the next box put this formula:

=(WEEKDAY(A2,2)>5

Then click on the Format button, and click on Colour (to affect the
foreground colour) and choose the colour you want (eg red). You could
also click on Bold, and if you click on the Patterns tab this affects
the background colour - you might choose bright green. You need to
click OK twice to exit the dialogue boxes, and then any cells which
have weekend dates in the range you had selected will now appear red
on a bright green backgound.

Hope this helps.

Pete
 
S

Shane Devenshire

Hi Kerry,

You can use this short formula in the spreadsheet or in conditional
formatting

=MOD(B2,7)<2

Assuming your date is in B2.

Cheers,
Shane Devenshire
Microsoft Excel MVP
 
Top