Forum Discussion

Deleted's avatar
Deleted
Jul 21, 2017

turn off Excel date system 1900 and 1904?

Hello,

I have issue with calculating AGE in my data set. More precisely, I have dates of incorporation, some of them have format dd/mm/yyyy, but some of them are only years e.g. 1967 which transforms into 1905. Is thre some possibility to turn off the date system 1900/1904. Maybe to assign the 4 digit dates (yyyy) the date of January the 1st? I do not know how to do that.

 

Can anybody help?

excel
Excel on Mac

2 Replies

Sort By
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Jul 21, 2017

    Hi Pavel

     

    You could make all the date values as text  (highlight then go to Home > Number Format Drop down >Text

     

    Then if your data is in A1, A2 etc  then in B1 type this

     

    =IF(VALUE(A1)<2099,DATE(A1,1,1),TEXT(A1,"dd/mm/yyyy"))

     

    This will then convert any year to the 1st of Jan each year, and every other date will be shown as the original date.

     

    You can then calculate age  ( I often use the YEARFRAC function to do that)

     

    Cheers

     

     

     

     

     

     

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jul 21, 2017

    Hi Pavel,

     

    Actually, you can’t turn off the date system, but you can change the cells format that include years only.

    Just switch the format for that cells to General, or highlight them and hit CTRL + Shift + ~

Resources