Conditional Formatting for 1st working day of the month

tonyshortleighton

New Member
Joined
Mar 22, 2013
Messages
6
I have a worksheet that has a a row dedicated to working dates. I would like to apply conditional format that highlights the first working date of the month. Due to weekends and bank-holidays it isn't as easy as simply identifying the 1st of each month. Any thoughts on how I could apply conditional format to achieve this?

TueWedThuFriMonTueWedThuTue
19/03/201320/03/201321/03/201322/03/201325/03/201326/03/201327/03/201328/03/201302/04/2013

<tbody>
</tbody>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome to MrExcel.

You can use a Conditional Formatting formula like:

=A2=WORKDAY(DATE(YEAR(A2),MONTH(A2),0),1)

where A2 is the active cell when it's entered. You can add a Holidays argument to the WORKDAY part if you have a list.
 
Upvote 0
Tony,

Given that your row of dates appears to only show working dates then you could perhaps use this...

Sheet1

*
A
B
C
D
E
F
G
H
I
1
Tue
Wed
Thu
Fri
Mon
Tue
Wed
Thu
Tue
2
19/03/2013
20/03/2013
21/03/2013
22/03/2013
25/03/2013
26/03/2013
27/03/2013
28/03/2013
02/04/2013

<TBODY>
</TBODY>

Conditional formatting
Cell
Nr.: / Condition
Format
A2
1. / Formula is =IFERROR(MONTH(A2)+YEAR(A2)>MONTH(OFFSET(A2,0,-1))+YEAR(OFFSET(A2,0,-1)),TRUE)
Abc

<TBODY>
</TBODY>

<TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

I have included the IFERROR purely to be able to make the formula valid for your leftmost cell, which I am assumong will always be a first workday of month.

If your dates will always be for the same year then you could remove the +YEAR(**) element from the formula.
Hope that helps.
 
Last edited:
Upvote 0
Welcome to MrExcel.

You can use a Conditional Formatting formula like:

=A2=WORKDAY(DATE(YEAR(A2),MONTH(A2),0),1)

where A2 is the active cell when it's entered. You can add a Holidays argument to the WORKDAY part if you have a list.

Thanks for the response and welcome.

If I wanted to apply this to a range of cells it is simply a case of substituting A2 across the formula with the range i.e. =$E$11:$I$11=WORKDAY(DATE(YEAR($E$11:$I$11),MONTH($E$11:$I$11),0),1)

Only when I make them amends it doesn't look like it's working.

Thank You

Tony
 
Upvote 0
Select E11:I11 and use the formula:

=E11=WORKDAY(DATE(YEAR(E11),MONTH(E11),0),1)

The reference will adjust relatively for the other cells.
 
Upvote 0
Worked a treat, you know what's coming next don't you....holidays! ;)

We do have a separate work-sheet with the England & Wales holidays for 2013 - 2014. You make reference to adding the Holiday argument to the Workday part, how would I include this to look-up the holidays from the other worksheet?

Thanks

Tony
 
Upvote 0
Within the spreadsheet we have two worksheets. Worksheet 1 is the master sheet which includes the dates that you've managed to get working to format the 1st day of the month. The second worksheet contains the holidays. They run from a2 to a14 and appear like this:

01/01/2013
29/03/2013
01/04/2013
06/05/2013
27/05/2013
26/08/2013
24/12/2013
25/12/2013
26/12/2013
27/12/2013
30/12/2013
31/12/2013
01/01/2014

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top