How to calculate a full year date difference?

[riaz]

I have a report that usually covers a full year (1 Jan to 31 Dec). Sometimes it may start or end at dates other than these two, so my header would change to "period" instead of "year".

A difference between the two, gives 365 in leap years and 364 in normal years, so that is not a useful test.

What can I use to get Excel to recognise that 1 Jan to 31 Dec is a full year, anything else is not?

 

[Kaps_mr2]

try :-

where F21 contains the end of your "year" and F22 the start of your "year".

=IF(AND(DAY(F21)=31,MONTH(F21)=12,DAY(F22)=1,MONTH(F22)=1),"FULL YEAR","PERIOD")

Thanks

Kaps

 

[RoryA]

Assuming dates in A1 and B1, how about:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1=B1

PS There's no acute accent in 'manger'.

 

[riaz]

Resolved Re: How to calculate a full year date difference?

Kaps, Rory, thank you both. Rory, I have to put in an IF statement to use yours, but both work fine. Spoilt for choice I am, I think I will count the letters in the formulae and use the one where I have to type less

And Rory, signature edited, thanks. I'm no francophone... (are you?)

 

[RoryA]

Re: Resolved Re: How to calculate a full year date difference?

I have my moments!

 

[Kaps_mr2]

c'est un moment !!!

 

[riaz]

My preference is for

To the water - This is the hour!!
(â l'eau, c'est l'heure)

 

[RoryA]

The less said about that, the better!

 

[Stormseed]

Mr. Riaz - dont forget that this thread is *not* in the lounge and also that you initially asked a question. Let it be a question