Excel formula query

  freddy-firecracker 13:31 14 Nov 06
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 06

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 06

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 06

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

  freddy-firecracker 15:31 14 Nov 06

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 06

The SUMPRODUCT should work then.

  freddy-firecracker 17:52 14 Nov 06

VoG

Thanks the formula was spot on

FF

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…