Number of days between two dates but include current date in the count

[Jimmib78]

Hi Everyone

Really hoping i can get some help here with the below problem.

I want to work out the days between two dates but also include the start day in the count. I then have then split into 4 different potential periods. I've tried the simple solution of minusing the dates in the example.

7/7/15 - 20/7/15 The difference here is 14 day including the 7/7 date. The problem is when i do the normal subtraction e.g A2-A3 is doesnt pick up the starting day (7/7) and shows 13 days. I know i can add +1 but this then shows a 1 in the cells which should be blank.

I then have a separate cell shows the total number of days added together from separate spells

I've tried using for example =DATEDIF(B14-1,E14,"D") in the example below:

Date Date Total days

Maternity - start 07/07/2015 Maternity - end 20/07/2015 14

SPL period 1 - start 21/07/2015 SPL period 1 - end 13/06/2016 329

SPL period 2 - start 21/07/2015 SPL period 2 - end 03/08/2015 14

SPL period 2 - start 17/08/2015 SPL period 3 - end 23/08/2015 7

SPL period 2 - start SPL period 4 - end #NUM!

Total leave days taken #NUM!

This does work but only if all the date periods are completed which isnt always the case, this also knocks out total leave days (sumof) because of the blank cells.

Does anyone have any ideas which will give me the result i need and no messages in the cells? Any help would be massively appreciated.

Thanks

J

[Jonmo1]

It should just be

=B1-A1+1

A1 = Start Date
B1 = End Date


Now you say it 'doesn't work' when both dates are not completed.
What do you want the result to be in that case, left blank?
Try
=IF(COUNT(A1,B1)=2,B1-A1+1,"")

[nigelbloomy]

You can wrap an IFERROR() around your formula like this

Please Login or Register  to view this content.

[Ron Coderre]

With your sample data in A1:D6
this formula, copied down, returns the count of days where start and end dates exist

Please Login or Register  to view this content.

Is that something you can work with?

[Jimmib78]

Thanks all, I really appreciate the help. This has done the trick and is working perfect now.

Even better is that i actually understand it too! Will continue trying to up my knowledge level.

Thanks again guys

J

[Jonmo1]

You're welcome.