Fix calendar setup

[Lux Aeterna]

I have a calendar setup in Excel where the first day of the month is dynamically placed in the first cell (C4). The subsequent cells (E4, G4, I4, K4, M4, O4) are populated with the following formulas. However, when the first day of the month falls on a Saturday or Sunday, the first day of the month is skipped, and I need to prevent this from happening.

The issue is that if the first day of the month is either a Saturday or Sunday, it is skipped and the formula doesn't place it correctly in the first available cell.

For example, in June 2024, cell C4 shows June 3rd, but it should be blank. Instead, cell M4 should display June 1st.

Here are the current formulas:
C4 (First Cell):

=IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)<4,DATE(YEAR($U$4),MONTH($U$4),1)+(3-WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)),"")

E4 (Second Cell):

=IF(C4="",IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=4,DATE(YEAR($U$4),MONTH($U$4),1),""),C4+1)

G4 (Third Cell):

=IF(E4="",IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=5,DATE(YEAR($U$4),MONTH($U$4),1),""),E4+1)

I4 (Fourth Cell):

=IF(G4="",IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=6,DATE(YEAR($U$4),MONTH($U$4),1),""),G4+1)

K4 (Fifth Cell):

=IF(I4="",IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=7,DATE(YEAR($U$4),MONTH($U$4),1),""),I4+1)

I wrote the formulas for M4 and O4 myself, so they might be incorrect.

M4 (Sixth Cell):

=IF(K4="",IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=7,DATE(YEAR($U$4),MONTH($U$4),1),""),K4+1)

O4 (Seventh Cell):

=IF(M4="",IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=7,DATE(YEAR($U$4),MONTH($U$4),1),""),M4+1)

Thank you in advance!

 

[MARK858]

If you are using 16 as the return type then Saturday is 1, Sunday is 2, Monday is 3 etc.

 

[Lux Aeterna]

If you are using 16 as the return type then Saturday is 1, Sunday is 2, Monday is 3 etc.

Thanks, but I am not sure what that means and how to adjust the formulas. A forum member had helped me with that.

By the way, the &lt;4 in the first formula is <4

 

[MARK858]

It means
M4 should be

IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=1

rather than

IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=7

and
O4 should be

IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=2

rather than

IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=7

If C4 is <4 then it will display a value if the 1st is on a Saturday, Sunday or Monday

 

[Lux Aeterna]

It means
M4 should be

IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=1

rather than

IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=7

and
O4 should be

IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=2

rather than

IF(WEEKDAY(DATE(YEAR(U4),MONTH(U4),1),16)=7

Thanks, I fixed those formulas. I think that there's still a problem in C4-K4. This is what I get (Δευτέρα means Monday). Instead, everything should be blank and M4 should be 1/6/2024.

 

[MARK858]

See my edit in the last line of my previous post, Monday is a 3

 

[Lux Aeterna]

See my edit in the last line of my previous post, Monday is a 3

That works great, thanks a lot!

 

[MARK858]

You're welcome

(Δευτέρα means Monday)

I know, my brother has lived in Kifissia for 40 years so I have picked up some Greek