Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekdays
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekdays
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 On Jul 4, 2:47*pm, Kerryn wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekdays
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: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekdays
The colour thing sounds even better. How would I do that?
Thanks!! "Pete_UK" wrote: 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 On Jul 4, 2:47 pm, Kerryn wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekdays
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 "Kerryn" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekdays
Pete_UK wrote:
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. 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 -- This space left blank intentionaly |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekdays
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 On Jul 4, 3:04*pm, Kerryn wrote: The colour thing sounds even better. *How would I do that? Thanks!! "Pete_UK" wrote: 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 On Jul 4, 2:47 pm, Kerryn wrote: 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?- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekdays
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 "Kerryn" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weekdays not using NETWORKDAYS | Excel Worksheet Functions | |||
Weekdays only! | Excel Discussion (Misc queries) | |||
WEEKENDS VS. WEEKDAYS | Excel Discussion (Misc queries) | |||
Number of Weekdays | Excel Worksheet Functions | |||
Weekdays | Excel Discussion (Misc queries) |