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.

wire1's avatar
wire1
Copper Contributor
Dec 20, 2019

Saty_Rana 

So here I created a sample table (sheet 1) how I did it all the time.

If you turn to sheet 2, I tried what Sergej had suggesting to me.

Now my question is, how will that work if I wish to switch that sheet 2 to 2020 with only adjusting one entry? I have to connect the days of the week to the dates, but how to, please? But that will not be the last step...

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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.

  • wire1's avatar
    wire1
    Copper Contributor
    Dec 20, 2019

    SergeiBaklan 

    thanks, Sergei, that is what I need.

    Many thanks. I will modify the tables at work which I need, all is fine.

    I wish you a nice festive season and a fine new year!

     

    • 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.

Resources