AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Now, to get the total number of months that have passed (not counting the completed years), you can use the below formula: = DATEDIF ( B1, B2, "YM" ) The below formula will give you the age in years (i.e., the total number of years that have passed in between the two given dates): = DATEDIF ( B1, B2, "Y" ) Suppose you have the same dataset and you want to calculate the age in years as well as months and days. But to get the age in years as well as in months and days would need slightly different formulas.Īgain, you can use the DATEDIF function to calculate the age which has been broken down by years, months and days. If you only want to calculate the age in years, you can use the formulas covered above. This will prevent any chances of error – most common could be having a date that Google Sheets doesn’t recognize as a date Calculate Age in Google Sheets – Number of Years, Months, and Days Note: When working with dates in Google Sheets, if you want a specific date in a cell, it’s best to use the DATE function to get it. Since we only want to get the age in years, I have used the INT function, which only gives us the integer part of the age. The YEARFRAC function will give you the number of years (including fractional years), between two given dates. Using the YEARFRAC FormulaĪnother way to calculate the age in years in Google Sheets is by using the YEARFRAC function.īelow is the formula that will give you age in years when you have the date of birth and the current date: =INT(YEARFRAC(B1,TODAY())) These will become a lot more clear when we look at more examples in the next section. “YD” – this will give you the total number of days that have passed between the two given dates but doesn’t count the ones in the years that have been completed.“YM” – this will give you the total number of months that have passed between the two given dates but doesn’t count the ones in the years and months that have been completed.“MD” – this will give you the total number of days that have passed between the two given dates but doesn’t count the ones in the Years and Months that have been completed.“D” – this will give you the total number of days that have passed between the two given dates.“M” – this will give you the total number of months that have passed between the two given dates.“Y” – this will give you the total number of years that have passed between the two given dates.But you can also use the below formula to do this (where the current date is automatically calculated using the TODAY function within the formula itself): = DATEDIF ( B1, TODAY ( ), "Y" )Īlso, let me give you all the other units you can use in the third argument of the DATEDIF function (we will be using these in the next section where we need to calculate age in years months, and dates in Google Sheets).īelow are all the values you can use in the unit argument in the DATEDIF function Note that I have used the current date as a cell reference (where I already have the current date in cell B2). In this example, I have used “Y”, which tells the formula to only calculate the total number of years that have elapsed between the two given dates. Unit – this is the argument that tells the DATEDIF function what needs to be calculated.End Date – this is the current date in this example.Start Date – this is the date of birth in our example.The below formula will calculate the age in the total number of years between the two given dates: = DATEDIF ( B1, B2, "Y" ) Let me first show you how to use the DATEDIF function. The first formula that you can use to do this is the DATEDIF formula (called the Date Difference formula), and the second one is using the YEARFRAC function. Now there are two formulas you can use to calculate the age in Google Sheets in years (i.e., to get the total number of years that have elapsed between the two given dates). Note that I have used the TODAY function to get the date of birth in cell B2. Suppose you have the dataset as shown below where you have the date of birth in cell B1 and the current date in cell B2. Calculate Age in Google Sheets – Number of Years, Months, and DaysĬalculate Age in Google Sheets – Number of Years.Calculate Age in Google Sheets – Number of Years.
0 Comments
Read More
Leave a Reply. |