Share via

Excel Calendar...Using Formulas!

Anonymous
2016-06-17T17:25:18+00:00

I am trying to create an attendance spreadsheet for each year + years to follow.  I' wanting it to have all 12 months on one page.

So I've got the format and view the way I am wanting it.  Now I just need the FORMULA to make it work, not VBA...  In the top Right Corner I have the date 01/01/2016 entered...  how do I get everything else to adjust based on the first day of the year?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-06-18T10:54:35+00:00

    That does work but not the way I'm looking for, below is the table format that I am using.  I want to change the date from year-to-year in the right corner (Bolded 2016) and all of the dates in the month change to the correct day it would fall on in the months

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2016-06-18T04:06:32+00:00

    Hi,

    try this...

    . 

    1) HORIZONTALLY

    in a new sheet..

    step1                     

    in cell A1 type the first year/date ( 1/1/2016 ) 

    step2                     

    in cell A2 write                     

    =DATE(YEAR($A$1),MONTH(A1)+1,1)                    

    and copy down up to cell A12 

    step3                     

    3a) in cell B1 write                     

    =A1+1 and copy down up to cell B12 

    3b) select the range B1:B12 and copy to the right up to AE1:AE12

    3c) delete the cells

    AC2:AE2, AE4, AE6, AE9 and AE11

    step4

    in cell AC2 write

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

    xxxxxxxxxxxxxx

    if you want....

    step5

    select the range A1:AE12

    and Custom Format ddd, d-m-yyyy

    '#############################

    in a new sheet.....

    2) VERTICALLY

    step1                     

    in cell A1 type the first year/date ( 1/1/2016 ) 

    step2                     

    in cell A2 write 

      =A1+1 

     and copy down up to cell A31

    step3 

    in cell B1 write                   

    =DATE(YEAR($A$1),MONTH(A1)+1,1)

    and copy to the right up to cell L1

    step4

    4a) select the range A2:A31 and copy to the right up to L2:L31

    4b) delete the cells

    B29:B31, D31, F31, I31 and K31

    4c) in cell B29 write

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

    xxxxxxxxxx

    if you want....

    step5

    select the range A1:L31

    and Custom Format ddd, d-m-yyyy

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2016-06-18T15:35:26+00:00

    https://1drv.ms/x/s!AjkE1kvSo-kqhXGUSn8xic7g7GOy

    download and edit this file

    Note

    Local settings

    1. in formulae semicolon (;)
    2. date format dd/mm/yyyy

    so,

    1/2/2016 refers to Feb. 1, 2016

    xxxxxxxxxx

    1. cell A1 define name myYear
    2. data in AB1:AB201, define name myList
    0 comments
  4. Anonymous
    2016-06-18T11:33:18+00:00

    Hi,

    Just download a template ...

    File, new, calendar ... search

    Full year global calendar.

    0 comments