Auto update date to match days??

S

squirrellydw

Board Regular
Joined
Apr 2, 2015
Messages
102
Office Version
  1. 365
Platform
  1. Windows
The pic below show a spreadsheet I'm working on. Top left in A1 is the current YEAR. In the calendar layout below I would like to have the day number show up in the top row of each month and to go under the correct day that is listed. For example today is Sunday March 2nd so I would like it to show up in the top row of March under Sunday. Then when the year 2026 comes along change the year at the top and have all the dates move correctly. How can I do this?

Auto Date.png
 
Sort by date Sort by votes
Like this?

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
12025
2
3
4
5
6SMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSM
7January 12345678910111213141516171819202122232425262728293031
8 
9February 12345678910111213141516171819202122232425262728
10 
11March 12345678910111213141516171819202122232425262728293031
12 
13April 123456789101112131415161718192021222324252627282930
14 
15May 12345678910111213141516171819202122232425262728293031
16 
17June123456789101112131415161718192021222324252627282930
18 
19July 12345678910111213141516171819202122232425262728293031
20 
21August 12345678910111213141516171819202122232425262728293031
22 
23September 123456789101112131415161718192021222324252627282930
24 
25October 12345678910111213141516171819202122232425262728293031
26 
27November 123456789101112131415161718192021222324252627282930
28 
29December 12345678910111213141516171819202122232425262728293031
30
Sheet1
Cell Formulas
RangeFormula
B7:AI7,B29:AG29,B27:AK27,B25:AI25,B23:AF23,B21:AK21,B19:AH19,B17:AE17,B15:AJ15,B13:AG13,B11:AL11,B9:AI9,B8,B10,B12,B14,B16,B18,B20,B22,B24,B26,B28B7=IFERROR(LET(d,DATEVALUE(A7&" 1, "&$A$1),wd,WEEKDAY(d)-1,n,DAY(EOMONTH(d,0)),s,SEQUENCE(,n+wd,1-wd),IF(s<1,"",s)),"")
Dynamic array formulas.
 
Upvote 0
Solution
yes, that looks just what I want. What the best way to copy that into the sheet? Thank you
 
Upvote 0
Eric W said:
Happy to help! :cool:
Click to expand...
one more question, is there away to fill in the blanks in the top rows with a color automatically? Or even both rows, for example in January fill in B7 to D7 and B8 to D8 and do that for all the blanks. I want the cells under the day numbers not filled so I can input data into those, if that makes sense. Thanks
 
Upvote 0
Easy enough:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
12028
2
3
4
5
6SMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSM
7January 12345678910111213141516171819202122232425262728293031
8
9February 1234567891011121314151617181920212223242526272829
10
11March 12345678910111213141516171819202122232425262728293031
12
13April 123456789101112131415161718192021222324252627282930
14
15May 12345678910111213141516171819202122232425262728293031
16
17June 123456789101112131415161718192021222324252627282930
18
19July 12345678910111213141516171819202122232425262728293031
20
21August 12345678910111213141516171819202122232425262728293031
22
23September 123456789101112131415161718192021222324252627282930
24
25October12345678910111213141516171819202122232425262728293031
26
27November 123456789101112131415161718192021222324252627282930
28
29December 12345678910111213141516171819202122232425262728293031
30
Sheet1
Cell Formulas
RangeFormula
B7:AL7,B29:AK29,B27:AH27,B25:AF25,B23:AJ23,B21:AH21,B19:AL19,B17:AI17,B15:AG15,B13:AK13,B11:AI11,B9:AF9B7=IFERROR(LET(d,DATEVALUE(A7&" 1, "&$A$1),wd,WEEKDAY(d)-1,n,DAY(EOMONTH(d,0)),s,SEQUENCE(,n+wd,1-wd),IF(s<1,"",s)),"")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:AL30Expression=(B7<>"")textNO


I thought the 2nd row might be for something else. So you might want to delete the formulas from the even rows (B8, B10, etc.) so that would leave them empty for something else.

Also, this Conditional Formatting formula will just highlight anything that isn't a blank in the B7:AL30 range. So for a starting grid like this, only the dates will be highlighted. But if you add something in the next row, it will be highlighted too. Here are a couple variants if you prefer:

Excel Formula: 
=(B7<>"")*($A7<>"")
will only highlight the top (date) row.

Excel Formula: 
=IF($A7<>"",B7,B6)<>""
will always highlight both the date row and the row below it.

If you want the 2nd row to be a different color, you'd need another CF rule.
 
Upvote 0
Eric W said:
Easy enough:

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
12028
2
3
4
5
6SMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSM
7January 12345678910111213141516171819202122232425262728293031
8
9February 1234567891011121314151617181920212223242526272829
10
11March 12345678910111213141516171819202122232425262728293031
12
13April 123456789101112131415161718192021222324252627282930
14
15May 12345678910111213141516171819202122232425262728293031
16
17June 123456789101112131415161718192021222324252627282930
18
19July 12345678910111213141516171819202122232425262728293031
20
21August 12345678910111213141516171819202122232425262728293031
22
23September 123456789101112131415161718192021222324252627282930
24
25October12345678910111213141516171819202122232425262728293031
26
27November 123456789101112131415161718192021222324252627282930
28
29December 12345678910111213141516171819202122232425262728293031
30
Sheet1
Cell Formulas
RangeFormula
B7:AL7,B29:AK29,B27:AH27,B25:AF25,B23:AJ23,B21:AH21,B19:AL19,B17:AI17,B15:AG15,B13:AK13,B11:AI11,B9:AF9B7=IFERROR(LET(d,DATEVALUE(A7&" 1, "&$A$1),wd,WEEKDAY(d)-1,n,DAY(EOMONTH(d,0)),s,SEQUENCE(,n+wd,1-wd),IF(s<1,"",s)),"")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:AL30Expression=(B7<>"")textNO


I thought the 2nd row might be for something else. So you might want to delete the formulas from the even rows (B8, B10, etc.) so that would leave them empty for something else.

Also, this Conditional Formatting formula will just highlight anything that isn't a blank in the B7:AL30 range. So for a starting grid like this, only the dates will be highlighted. But if you add something in the next row, it will be highlighted too. Here are a couple variants if you prefer:

Excel Formula: 
=(B7<>"")*($A7<>"")
will only highlight the top (date) row.

Excel Formula: 
=IF($A7<>"",B7,B6)<>""
will always highlight both the date row and the row below it.

If you want the 2nd row to be a different color, you'd need another CF rule.
Click to expand...
That’s close but I wanted the blanks at the beginning and end of the top rows to be filled in with something like a light gray color but what you posted might work just as well. Thank you and yes the second row is used for something
 
Upvote 0
Sorry, I misunderstood your request. We just need to change the <> to =

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
12028
2
3
4
5
6SMTWTFSSMTWTFSSMTWTFSSMTWTFSSMTWTFSSM
7January 12345678910111213141516171819202122232425262728293031
8
9February 1234567891011121314151617181920212223242526272829
10
11March 12345678910111213141516171819202122232425262728293031
12
13April 123456789101112131415161718192021222324252627282930
14
15May 12345678910111213141516171819202122232425262728293031
16
17June 123456789101112131415161718192021222324252627282930
18
19July 12345678910111213141516171819202122232425262728293031
20
21August 12345678910111213141516171819202122232425262728293031
22
23September 123456789101112131415161718192021222324252627282930
24
25October12345678910111213141516171819202122232425262728293031
26
27November 123456789101112131415161718192021222324252627282930
28
29December 12345678910111213141516171819202122232425262728293031
30
Sheet1
Cell Formulas
RangeFormula
B7:AL7,B29:AK29,B27:AH27,B25:AF25,B23:AJ23,B21:AH21,B19:AL19,B17:AI17,B15:AG15,B13:AK13,B11:AI11,B9:AF9B7=IFERROR(LET(d,DATEVALUE(A7&" 1, "&$A$1),wd,WEEKDAY(d)-1,n,DAY(EOMONTH(d,0)),s,SEQUENCE(,n+wd,1-wd),IF(s<1,"",s)),"")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:AL30Expression=IF($A7<>"",B7,B6)=""textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Conditional Formatting for rows of empty cells corresponding with dates between
Replies
4
Views
738
avril18
A
W
  • Question Question
Date Planner from Start and End Dates
2
Replies
11
Views
403
Cubist
Cubist
J
  • Question Question
Spread Revenue over Date Range
Replies
1
Views
119
Cubist
Cubist
Z
  • Question Question
Sum Values by month from 1st day to last day of each month, and specific date range
Replies
6
Views
763
alansidman
alansidman
S
  • Solved
Display date using ordinal numbering
2
Replies
10
Views
504
6StringJazzer
6StringJazzer

Share this page

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