Excel formula query

  freddy-firecracker 13:31 14 Nov 2006
Locked

I'm trying to set up an excel calc sheet that calculates the difference between two dates without including leap years. I've tried using the day formula and transferring information from other cells to combine with this result but it still doesn't work.

I would appreciate any pointers.


Thanks


FF

  FreeCell 13:51 14 Nov 2006

Not sure I understand the significance of the leap year point. If you have two cells with dates e.g. 01/01/05 and 31/01/06 and subtract one from the other, displaying result as a positive number then it gives the answer of 395 (days).

There must be more to what you are looking to do.

  VoG II 13:54 14 Nov 2006

Or

=DATEDIF(A1, A2, "d")

where A1 houses the earlier date and A2 the later date. I think that excluding leap years is going to be messy. Do you actually want to exclude the whole year or just the extra day.

To find out if a year is a leap year

=DAY(DATE(A1,3,0))=29

will return TRUE if the year (stored as a number, not an Excel date) in A1 is a leap year.

  VoG II 14:29 14 Nov 2006

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"m/d")<>"2/29"))-1

  freddy-firecracker 15:31 14 Nov 2006

Vog

I just want to exclude the extra days not the years. In any four year period this figure should = 1. Over 46 years it would be 11 and so on.

  VoG II 17:05 14 Nov 2006

The SUMPRODUCT should work then.

  freddy-firecracker 17:52 14 Nov 2006

VoG

Thanks the formula was spot on

FF

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Aardman's new YouTube channel to share the work of independent animators

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017