Forum Discussion

wire1's avatar
wire1
Copper Contributor
Dec 19, 2019
Solved

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 simil...
excel
Formulas and Functions
Macros and VBA
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 20, 2019

    wire1 

    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 + 1

    For the day of the week you may repeat the date ( = A3 ) and apply to it custom format as

    Please check attached.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 20, 2019

wire1 , thank you, all the best for coming year and happy holidays!

wire1's avatar
wire1
Copper Contributor
Dec 20, 2019

SergeiBaklan 

Sorry for coming once more.

I was trying this with "each sheet 1 month".

I found that I hadn't formatted the value in A3 correctly first, so the calendar must be started with =DATUM(CurrentYear;1;1) for Jan. 1st, =DATUM(CurrentYear;2;1) for Feb. 1st, and so on. When changing from year 2019 to 2020, I get Feb. 29th. Not I am puzzled that until May, I get the weekdays with the simple formula =A3 for the first day of the month (the dates start with line 3 always).

But when I come to June, that same formula only returns the date I habe in column A, but not the day names. I can't find out what's wrong here.

Then, I tried to apply a rule for formatting the lines containing "Sa" (Saturday) and "So" (Sunday), and tried so with no effect at all. The rule I find in the rules manager, but I cannot apply it at all. It should affect the entire line inside the table. What am I missing? I thought by clicking on "ok" the rule would be applied.

I attach the new file here.

Thanks again.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 21, 2019

    wire1 

    1) For the Feb dates I'd suggest to modify formula for adding dates as

    = IF(MONTH(A3 + 1)=MONTH(A3),A3+1,"")

    With this we have 29 days on leap year and last blank cell for other years.

    2) Copy/paste SUM row from Jan into row #34 of each month, independently how many days in month

    3) For Jan day column formula to modify as

    =IF(A3="","",A3)

    and apply custom number format to B3:B33 as it is now - select B3:B33, Ctrl+1 and

    5) Now select B3:B33 in Jan, copy it and paste into B3:B33 for each other month (to shift from one sheet to another you may use Ctrl+PageDown and Ctrl+PageUp)

     

    With that you have universal calendar for any year. In your variant custom format was not applied to day in Jun.

    Please check attached.

Resources