Auto recognize weekends with month as row/day as column?

[lichldo]

Hello, I have a worksheet set up like this for us to paste in data we get from a daily report, and at the end we will have some percentage calculation, however there will be some columns with no data since they are weekends. Is there a way for excel to recognize those in this format and put a "-" or a "0" or a different color or something in those cells? We will want the formulas we put in column AH (after the 31st day) to exclude any cells that are weekends

 

[etaf]

does the format - just show TEXT - as it could refer to any year
you may want to put a year somewhere so it can be used

if you are putting data into the cells, then you will overwrite any formula setup
so you will probably need VBA - not my area

If you want a colour
then
=WEEKDAY(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B$1,"st",""),"nd",""),"rd",""),"th","")*1&"/"&MONTH(DATEVALUE($A2&"1"))&"/"&2022,2)>5

would be simplier if the month was actually a real date - formatted just to show the month - then no year needed
also if the day of month was just 1 , 2, 3 and not using the ordinals (st, nd, rd etc)

i have just done a little example as it takes time to setup

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th
2	january
3	february
4	march
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:P4	Expression	=WEEKDAY(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B$1,"st",""),"nd",""),"rd",""),"th","")*1&"/"&MONTH(DATEVALUE($A2&"1"))&"/"&2022,2)>5	text	NO

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

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