How to convert excel dates into decimal dates

I have found three methods to convert excels dates into decimal dates where the year is represented by the whole number and the months and days are represented by everything after the decimal place as a fraction of a year.
Method 1=(date/365.25)+1900
Method 2=year+(month/12)-(1/12)+(day/365.25)
Method 3=YEARFRAC(date,0,1)+1900
---------------------------------------------------------------------------------
#exceltutorials #exceltipsandtricks #excelformulas
SUBSCRIBE: goo.gl/c46YPs
VIDEO INFO: These data are related to my PhD research.
---------------------------------------------------------------------------------
IF YOU LIKE THIS VIDEO YOU MAY ALSO LIKE:
- How to find the closest date in excel • How to find the closes...
- Converting Julian days into date and time in excel • Converting Julian days...
- How to find the weekday in excel • How to find the weekda...

Пікірлер: 10

  • @elliotmccluskey
    @elliotmccluskey3 жыл бұрын

    Thanks so much!

  • @baatrmandzhiev3214
    @baatrmandzhiev32145 жыл бұрын

    Thank you, really interesting methods. I will use them

  • @srinivastata6829

    @srinivastata6829

    2 жыл бұрын

    What method use bro

  • @mariaczarnecka216
    @mariaczarnecka2163 жыл бұрын

    Thank you, that is very helpful and well explained. I used the first method. Could you just confirm for me, what would be the formula to do the opposite (convert from a decimal to the usual format date)?

  • @karinaadcock

    @karinaadcock

    3 жыл бұрын

    It would be = (decimal date - 1900) * 365.25

  • @QuinYouTube
    @QuinYouTube4 жыл бұрын

    This is the best way I have found: (If you have a date in A1) =YEARFRAC(DATE(YEAR(A1), 1, 1), A1, 1)+YEAR(A1)

  • @nauryzbekabylkair1618

    @nauryzbekabylkair1618

    4 жыл бұрын

    You solved problem with starting a new year from 30th December of previous year instead of 1st January. You are a genius! Thanks!!!

  • @Aparna_Yadav
    @Aparna_Yadav2 жыл бұрын

    For other years which are not a leap year then, why are we dividing every date by 365.25?

  • @karinaadcock

    @karinaadcock

    2 жыл бұрын

    If its a leap year you divide by 366, if its not a leap year you divide by 365, assuming your data covers multiple years and you don't want to go through the whole data set changing your formula back and forth, 365.25 is a compromise value, as a leap year happens every 4 years.

  • @Aparna_Yadav

    @Aparna_Yadav

    2 жыл бұрын

    ​@@karinaadcock Thank you so much for your reply. Very nicely you explained