Excel formula for date calculation

Question

Hi

having difficulty getting =DATEDIF function to work in Excel.

I have a 'start date' of column 'E,4' which is 28/08/2009 (UK date format) and in column 'M,4' I need to calculate the current age so it would appear as 8.1 years but this total needs to be a running total so that whenever opened the actual age to nearest month appears.

I have used the following formula but I get the dreaded #NUM! appear in the M,4 cell

=DATEDIF(TODAY(),E4,"y,m")

I appreciate any help and advice on this.

Thanks

Answer 1

Hi

I found the solution on the web page below and adapted it for displaying it to look like a digital format

=DATEDIF(E4,TODAY(),"Y")&"."&DATEDIF(E4,TODAY(),"YM")

https://www.thoughtco.com/calculate-current-age-datedif-function-3123763

Answer 2

Thanks for your fast reply, it doesn't work as '8 years 30 days' is too long so I will leave it as '8.0'

Thanks for your help

Answer 3

Hi

thanks for your fast reply, the =DATEDIF(E4,TODAY(),"y") works and shows 8.0 years but is there a way to show months as well and in this case it would be 8.1 years?

Thanks

As I think NO. The formula returns 30 days but not a month. You can mention it as I've shown in my earlier post using Concatenate.

Answer 4

Hi

thanks for your fast reply, the =DATEDIF(E4,TODAY(),"y") works and shows 8.0 years but is there a way to show months as well and in this case it would be 8.1 years?

Thanks

Answer 5

DATEDIF Function works like below for your understanding:

=DATEDIF(Start_Date,End_Date,"Parameter")

You have entered: =DATEDIF(TODAY(),E4,"y,m") where 2 wrongs:

1. Today() is always greater than E4, so if you deduct then a - (negative) figure will show and it returns #NUM error.
2. Second wrong is you have entered 2 parameter with "" (Double quotation mark). You need to use just once.

Like below:

=DATEDIF(E4,TODAY(),"y") will show you 8 years

=DATEDIF(E4,TODAY(),"Ym") will show you months after deducted all the years

=DATEDIF(E4,TODAY(),"Md") will show you the remaining days after Months deducted

So, for getting the age result you can use this:

=CONCATENATE(IF(DATEDIF(E4,TODAY(),"Y")=0," ",DATEDIF(E4,TODAY(),"Y")&" Year "),IF(DATEDIF(E4,TODAY(),"YM")=0,"",DATEDIF(E4,TODAY(),"YM")&" Month "),IF(DATEDIF(E4,TODAY(),"MD")=0,"",DATEDIF(E4,TODAY(),"MD")&" Day "))