# Dates Conditional Formatting



## ozbeachbum (Dec 21, 2022)

Hi All,
I am endeavouring to CF the 6th day of each month, I came up with the following formula, however it highlights every day of the month.
=G$10-DAY(G$10)+6
G10 is Friday 01-July-2022, if I use the formula just in a cell it returns 06-July-2022 and I have the $ if front of the 10 to make it absolute.
Any ideas greatly appreciated.
Cheers,
Dave.


----------



## etaf (Dec 21, 2022)

i cant see the images

=Day(G$10)=6

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore - 

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.








						XL2BB - Excel Range to BBCode
					

Excel 'mini-sheet' in messages - XL2BB  Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...




					www.mrexcel.com
				




You can also test to see if it works ok, in the "Test Here" forum. 








						Test Here
					

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc.  Threads in this forum are automatically deleted after no replies for seven (7) days




					www.mrexcel.com
				




OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed


Test Data-ETAF2.xlsxBC171/1/221181/2/222191/3/223201/4/224211/5/225221/6/226231/2/222241/3/223251/4/224261/5/225271/6/226281/2/222291/3/223301/4/224311/5/225321/6/226Sheet1Cell FormulasRangeFormulaC17:C32C17=DAY(B17)Cells with Conditional FormattingCellConditionCell FormatStop If TrueB17:B32Expression=DAY(B17)=6textNO


----------



## ozbeachbum (Dec 21, 2022)

Hi,
Not sure why, they open ok on my end.
I will have to look into it and get back.


----------



## etaf (Dec 21, 2022)

ok,  I'm on a small macbook and they do open, just difficult to have to zoom in and look around 

did you see my suggestion ?


----------



## ozbeachbum (Dec 21, 2022)

Hi,
I have saved them in paint, let me know if can view them now.
I would attach a mini sheet except that since upgrading to W11 & Office 21 it says it copies it but it doesn't, and all the feedback I have received has said I am not going to resolve the issue.
Your formula =Day(G$10)=6 removed all the highlighting, but did not highlight the 6th day.
Thank you for your time and hope that this info may assist you in finding a solution for me.


----------



## etaf (Dec 21, 2022)

ok, thanks - still not able to follow - but thanks for going to the trouble 

I cannot see whats in G10 - is it a date ? or text 

if day(g10)=6 , did not work, i suspect its not a date 

this is what i see zoomed in


----------



## ozbeachbum (Dec 21, 2022)

G10 is a date, customised to ddd dd, showing Fri 01.


----------



## etaf (Dec 22, 2022)

ok
so if you put in a spare cell 
=Day(G10) 
what do you get , it should be 1 , as its the 1st 
Day() gives you the day of the month - so 1 
G10 will need to show 
6th of a month to work 
so Fri 01 - will be NOT conditional format 
not sure what range you are selecting to conditional format 

but my example shows a range and highlight where day is the 6th


----------



## ozbeachbum (Dec 23, 2022)

Hi,
Thanks for your patience and effort, if mini sheet was working for me you would be see what is happing.
If I put =DAY(G10) in spare cell I get 1 as you point out, if I put =DAY(G$10)+5 in a spare cell I get Fri 06, therefore I thought that if I put it in conditional formatting it would highlight only the 6th day of each month, but it highlights every cell in the range which covers every day of the year except those that match the two conditions before it.
I don't think that this has any bearing, but just a little extra information, the cell range in CF is;
=$G$10:$G$40,$N$10:$N$40,$U$10:$U$40,$AD$10:$AD$40,$AK$10:$AK$40,$AR$10:$AR$40,$BA$10:$BA$40,$BH$10:$BH$40,$BO$10:$BO$40,$BX$10:$BX$40,$CE$10:$CE$40,$CL$10:$CL$40
Column G being July, N August and so on.
Have a very Merry Xmas, I trust the weather is not to bad in your part of the UK.
Cheers,
Dave.


----------



## etaf (Dec 23, 2022)

conditional formatting , needs the formula to result in either TRUE or FALSE - not a zero is also seen as FALSE and 1 or more TRUE
so
=DAY(G$10)+5 will equal 6 - and probably be seen as TRUE - as a 0 is false and 1 or more TRUE

so you would need
=(DAY(G$10)+5)=6
then it will highlight if indeed the day in G10 was 1


----------



## ozbeachbum (Dec 21, 2022)

Hi All,
I am endeavouring to CF the 6th day of each month, I came up with the following formula, however it highlights every day of the month.
=G$10-DAY(G$10)+6
G10 is Friday 01-July-2022, if I use the formula just in a cell it returns 06-July-2022 and I have the $ if front of the 10 to make it absolute.
Any ideas greatly appreciated.
Cheers,
Dave.


----------



## etaf (Dec 23, 2022)

> Have a very Merry Xmas, I trust the weather is not to bad in your part of the UK.


Hope you and family also have a great holiday, probably on a beach   unlike our lots of rain and local flooding


----------



## ozbeachbum (Dec 23, 2022)

Everything you say is so true and makes sense, but unfortunately still does not work.
I gather that you think the issue is that G1O is not returning "1".
I have done the following in spare cells, with results indicated;
=DAY(G$10)           Returns "1" Formatted General.
=DAY(G$10)+5       Returns Fri 06 Formatted Date Ddd dd.
=(DAY(G$10)+5)=6 Returns "TRUE".
so I am at a loss as to why when it is entered into CF it does not work and highlights all referenced cells.
Again just for info that might help, if I take the $ out it only highlights G10.


----------



## ozbeachbum (Dec 23, 2022)

Keep safe.
Our area was hit badly by floods earlier in the year.


----------



## etaf (Dec 23, 2022)

$ fixes the reference 
so no matter what range you have selected by conditional formatting - it will always look in row 10 

so G10 , H10, I10 etc 

Book5ABCDEFGHIJ12345678G10G10+591001 01/01/202206 06/01/20221112conditional formatting formula13TRUEFALSEFALSE14151617Sheet1Cell FormulasRangeFormulaI10I10=G$10+5G13:I13G13=(DAY(G$10)+5)=6Cells with Conditional FormattingCellConditionCell FormatStop If TrueG10:I10Expression=(DAY(G$10)+5)=6textNO

G10 has the 1/1/22 in 
so day() =1 

formula in conditional formatting 
NOTE selected area is G10 to I10 
the formula and row need to match , to test the cell and highlight 

so you can see the only cell that highlights is G10 

maybe try it out on a small sample and get it working and then try in the real sheet

this is what i did

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
* Conditional Formatting *

Highlight applicable range >> 
 G10:I10

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true: 
=(DAY(G$10)+5)=6

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK


----------

