Calculate how many days in a year that falls between two dates

**shaunguyver** · 05-13-2016, 07:05 AM

Hi all,

I have two dates - a start date (cell A1) and an end date (cell B1)

I then have years 2016, 2017, 2018 etc in cells C1 onwards.

I need a calculation that provides the numbers of days underneath each year that falls into those two dates.

So for example, if the start date is 01 June 2016, and the end date is 31 Jan 2018, it would look like this:

2016: 210 (01 June to 31 December)
2017: 365 (whole year)
2018: 31 (01 to 31 Jan)

Is there a calculation that can perform this? I've tried, and failed.

Thanks

**FoxSeaLady** · 05-13-2016, 07:25 AM

Hi

I have attached a spreadsheet with a formula that may help

Cheers

**samba_ravi** · 05-13-2016, 07:30 AM

I think there is 214 days in 2016 if yes try below formula

Please Login or Register  to view this content.

and copy towards down

**Tony Valko** · 05-13-2016, 07:33 AM

Try this...

Data Range

	A	B	C	D	E	F	G	H
1	Start	End	2015	2016	2017	2018	2019	2020
2	3/27/2016	12/1/2018	0	280	365	335	0	0

This formula entered in C2 and copied across:

=MAX(0,MIN(DATE(C1+1,1,1),$B2+1)-MAX(DATE(C1,1,1),$A2))

**shaunguyver** · 05-13-2016, 07:47 AM

Thank you very much. The problem is that I'm working in Excel 2007, is there any way of achieving this in Excel 2007? The =DAYS function isn't supported, sadly.

**JohnTopley** · 05-13-2016, 07:52 AM

Use the DATE formulas provided Tony and Siva

**shaunguyver** · 05-13-2016, 08:10 AM

Thank you Tony Valko, that worked perfectly!

**Tony Valko** · 05-13-2016, 08:12 AM

You're welcome. Thanks for the feedback!

Calculate how many days in a year that falls between two dates

LinkBack

Thread Tools

Rate This Thread

Display

Calculate how many days in a year that falls between two dates

Re: Calculate how many days in a year that falls between two dates

Re: Calculate how many days in a year that falls between two dates

Re: Calculate how many days in a year that falls between two dates

Re: Calculate how many days in a year that falls between two dates

Re: Calculate how many days in a year that falls between two dates

Re: Calculate how many days in a year that falls between two dates

Re: Calculate how many days in a year that falls between two dates

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] calculate total number of days between two days based on 365 days year

if the 15th of the month falls between two dates, calculate, otherwise 0

Calculate Number of Days between multiple dates in the same calendar year for pror

Calculate Number of Days between multiple dates in the same calendar year for pror

Calculate number of days in a particular month/year between two dates

Calculate difference between two dates in days, months and year

count no. of dates in a column that falls on certain month & year

Bookmarks

Bookmarks

Posting Permissions