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.

Saty_Rana's avatar
Saty_Rana
Brass Contributor
Dec 20, 2019

ctrl + H to use find and replace tool, find 2019, replace with 2020

Saty_Rana's avatar
Saty_Rana
Brass Contributor
Dec 20, 2019
can you send the original file , instead of .jpg form? just asking.
  • 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!

         

  • wire1's avatar
    wire1
    Copper Contributor
    Dec 20, 2019
    Sorry, that's job data, I'm not allowed to.
    I'd have to try and create a table similar to that at the job and upload it here.

Resources