Calculating the number of days within differing years

[Aardvark MarG]

Summary: I first calculate the number of days between two dates (a Start and a Finish date) using DATEDIF.

I then need to show those days split (in a series of cells to the right) into different financial years (which each run 1st Apr to 31st Mar) according to which ever the Start and Finish dates fall within.

A number of days could therefore all be within a single year, or may have a few from the tail end of Yr1, be all of Yr2, be all of Yr 3 and finally a few from the start of Yr4.

I have tried and failed badly to sort this out using nested IF Functions DAYS360 and DATEDIF.

If anyone could be of asistance with this I'd be very grateful. Thanks, MarG.

[shg]

Welcome to the forum, Aardvark.

Post a workbook.

[daddylonglegs]

If you have start date in A3 and end date in B3 then this formula will give the days in the year

=MAX(0,MIN($B3,D$2)-MAX($A3,D$1)+1)

Where D1 is the start date of the year and D2 the end date. You can list all the years and copy the formula across and down as necessary, see attached.

Note: the formula counts both start and end date (you can calculate differently if you want)