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

Slyon

New Member
Joined
Jul 26, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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")

1634814867488.png


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! ?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,762
Office Version
  1. 365
Platform
  1. Windows
If that last pair of dates should count as one day, surely the other two pairs should count as 32 days not 31
 

Slyon

New Member
Joined
Jul 26, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,762
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=IF(F3<>"",F3-E3+1,0)+IF(H3<>"",H3-G3+1,0)+IF(J3<>"",J3-I3+1,0)
 

Slyon

New Member
Joined
Jul 26, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Ok, how about
Excel Formula:
=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.. ?

1634822471453.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,762
Office Version
  1. 365
Platform
  1. Windows
It works for me.
+Fluff 1.xlsm
DEFGHIJ
1
2
3910/09/202112/09/202110/09/202112/09/202110/09/202112/09/2021
4610/09/202112/09/202110/09/202112/09/2021
5710/09/202112/09/202110/09/202112/09/202113/09/202113/09/2021
60
Main
Cell Formulas
RangeFormula
D3:D6D3=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.
 
Solution

Slyon

New Member
Joined
Jul 26, 2021
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
It works for me.
+Fluff 1.xlsm
DEFGHIJ
1
2
3910/09/202112/09/202110/09/202112/09/202110/09/202112/09/2021
4610/09/202112/09/202110/09/202112/09/2021
5710/09/202112/09/202110/09/202112/09/202113/09/202113/09/2021
60
Main
Cell Formulas
RangeFormula
D3:D6D3=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. (y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,762
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,181,695
Messages
5,931,489
Members
436,790
Latest member
aelbaitam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top