Forum Discussion
Calendar in Excel
Hi.
At the Job we have some statistics covering the entire year. Basically, it's a calendar type of table where you add specific values each working day.
Now, one year is done, and you need a similar thing for the following year.
Instead of adjusting manually each month from the old to the new year, there is some formula (?) or macro (?) so you have to adjust only one value in the old table to make it switch to the new one, as you see in these examples :
(Dec. 2019.jpg / Dec. 2020.jpg)
I know there is a fine trick to do that, but I never saw how it is done. Please update me.
Thankx.
You may add in any sheet (here in third one) helper cell with the year and name it as, for example, CurrentYear
When first date for the current year will be
=DATE(CurrentYear,1,1)next one as
=A3 + 1For the day of the week you may repeat the date ( = A3 ) and apply to it custom format as
Please check attached.
17 Replies
- use find and replace toolvto replace 2019 with 2020
ctrl + H to use find and replace tool, find 2019, replace with 2020
- can you send the original file , instead of .jpg form? just asking.
- "Find and replace tool"?
Sorry, apparently I am not that deep into Excel....
Maybe I've heard of that, and never learned what it is.
Please could you explain in smaller steps. Thank you.
It depends on how your current calendar table is built. In general
next date = previous date+1
and weekends could be highlighted by conditional formatting.
If such way you need only to change one starting date.
I'm not sure I understand, I'm afraid.
Of course "next day" is "actual day + 1".
My question is, if I have such a table in Excel for 2019, how can I build it to be able to convert it with one data entry from 2019 to 2020, and all data and week days will be adjusted?
If you have the table for 2019 where is only on date is hardcoded, e.g. 01 Jan 2019, when you may copy/paste it on 2020 and change this first date on 01 Jan 2020.
