How to class 'From' and 'To' dates that have the same date as 1 day, when counting amount of days from several dates.

[Slyon]

Hi all,

I was wondering if someone could help me.

I am trying to count the number of days between dates in certain columns, which I've managed to do successfully to a certain point.., however excel isn't picking up the 'From' and 'To' dates that are the same date as one day, if that makes sense?

This is the formula I've used so far to add each date range together, to then show that total where I've circled below: =DATEDIF(E3,F3,"d")+DATEDIF(G3,H3,"d")+DATEDIF(I3,J3,"d")

The 'Total Days Taken' column needs to be formulated in a way that if someone else uses the spreadsheet it will work it out for them, so their not having to figure out how to change it for certain dates etc.

Appreciate any help, as I'm pulling my hair out trying to understand whether what I'm asking cant be done! ?

 

[Fluff]

If that last pair of dates should count as one day, surely the other two pairs should count as 32 days not 31

 

[Slyon]

If that last pair of dates should count as one day, surely the other two pairs should count as 32 days not 31

Yes that sounds about right. I'm just not sure how to go about the whole formula to count the days correctly.

Those dates I've put in are just random to use as an example and to see how the formula was working.

 

[Fluff]

Ok, how about

=IF(F3<>"",F3-E3+1,0)+IF(H3<>"",H3-G3+1,0)+IF(J3<>"",J3-I3+1,0)

 

[Slyon]

Ok, how about

=IF(F3<>"",F3-E3+1,0)+IF(H3<>"",H3-G3+1,0)+IF(J3<>"",J3-I3+1,0)

Thank you, that almost worked! I've tried pasting this into the relevant boxes and this is the outcome:

The 1st line should total 9 days, 2nd should total 6 days and 3rd should be 7 days. It seems there all a number off. Not sure why but if I remove the dates from the 'Absences 2' column everything totals how I need it to.. ?

 

[Fluff]

It works for me.

+Fluff 1.xlsm
	D	E	F	G	H	I	J
1
2
3	9	10/09/2021	12/09/2021	10/09/2021	12/09/2021	10/09/2021	12/09/2021
4	6	10/09/2021	12/09/2021	10/09/2021	12/09/2021
5	7	10/09/2021	12/09/2021	10/09/2021	12/09/2021	13/09/2021	13/09/2021
6	0
Main

Cell Formulas
Range	Formula
D3:D6	D3	=IF(F3<>"",F3-E3+1,0)+IF(H3<>"",H3-G3+1,0)+IF(J3<>"",J3-I3+1,0)

Check that you are looking at the right cells.

 

[Slyon]

It works for me.

+Fluff 1.xlsm
	D	E	F	G	H	I	J
1
2
3	9	10/09/2021	12/09/2021	10/09/2021	12/09/2021	10/09/2021	12/09/2021
4	6	10/09/2021	12/09/2021	10/09/2021	12/09/2021
5	7	10/09/2021	12/09/2021	10/09/2021	12/09/2021	13/09/2021	13/09/2021
6	0
Main

Cell Formulas
Range	Formula
D3:D6	D3	=IF(F3<>"",F3-E3+1,0)+IF(H3<>"",H3-G3+1,0)+IF(J3<>"",J3-I3+1,0)

Check that you are looking at the right cells.

I have no idea what I did earlier, but it has now worked second time round! Thanks for helping, this has been a massive help.

 

[Fluff]

You're welcome & thanks for the feedback.