Calculating Travel Days And Not 24 Hour Periods Between Two Dates

[TheAssetMgr]

If I start traveling on 8/1 and stop traveling on 8/3 then I have completed three travel days.

If my start date is in cell A1 and my stop date is in cell B1 what formula can I use to calculate the number of travel days please?

Thanks!

 

[Logit]

.

=DATEDIF(A1,B1,"d")

 

[TheAssetMgr]

.

=DATEDIF(A1,B1,"d")

Thanks but I've tried that already and it returns a value of 2 instead of 3. Adding a "+1" out to the right of the formula does get me to the right number but it also leaves a "1" in the field when the date values aren't yet populated and that "1" gets erroneously added to another calculation.

Any other ideas?

 

[Logit]

.

=IF(OR(A1="",B1=""),"",DATEDIF(A1,B1,"d")+1)

 

[TheAssetMgr]

.

=IF(OR(A1="",B1=""),"",DATEDIF(A1,B1,"d")+1)

Thanks Logit, that works perfectly!

Is there any way to make the result of that formula equal a zero instead of a blank if the two date fields are empty? My OCD kicked in when the cell next door displayed the standard #N/A because it was expecting a number instead of a blank.

 

[Logit]

.
If either A1 or B1 is empty and you want a ZERO to show instead of a blank cell in C1 you can use this formula :

=IF(OR(A1="",B1=""),"0",DATEDIF(A1,B1,"d")+1)

You mentioned " #N/A " ... I hope you mean before using the formulas I supplied because you should not being seing an #N/A any longer under any circumstances.

It is really late here now. Off to sleep. I'll look at this in the morning.


Cheers.

 

[TheAssetMgr]

Thanks for the quick turnaround and that DID put a zero in the cell. Must be the formula I'm using in the other cell that's causing the problem because it still says #N/A. It's not your code that produces the #N/A btw.

Is there a way to bring a screen shot into this reply? That would REALLY make it easier to explain things. I'll try it with words...

I have four columns: Date Out, Date In, Travel Days, Comp Days. Date Out and Date In are just regular date fields. Travel Days is what we're working on here and Comp Days is a formula someone else helped me with and it's designed to look at how many travel days there are and calculate the right number of comp days. The Comp Days formula, =LOOKUP(E7,{0,3,7,11,15,28,35,42},{0,1,2,3,4,5,6,7}), works well and returns the correct values.

When I just type a zero into the Travel Days cell I get a zero in the Comp Days cell, which is good. But, when I replace the plain zero in the Travel Days cell with your code that produces a zero the Comp Days cell produces the #N/A and I'm not sure why. Hope that makes sense but if not let me know and I'll try to explain more clearly.

Thanks for diving into this when it's late for you - much appreciated.

 

[Logit]

.
Good Morning !

Hopefully I am understanding your description correctly.

Paste this macro in the Sheet Level Module for your worksheet. It is written for A1, B1, C1 cells :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("C1")
    If Range("A1").Value = "" Or Range("B1").Value = "" Then
        Range("C1").Value = ""
    Else
        Range("C1").Value = (Range("B1").Value - Range("A1").Value) + 1
    End If
End With
End Sub

Delete the formula from C1 ... then enter dates in A1 & B1. Does the value in E7 work correctly if there are no dates in A1 or B1 ?

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	1-Sep-18	4-Sep-18	4
2		4-Sep-18	0
3	1-Sep-18	4-Sep-18	4	s/b correct. See edit of "0"
4		4-Sep-18	0	Shows "text"
1a

Cell Formulas
Range	Formula
C1	=(COUNT(A1:B1)=2)*(B1-A1+1)
C2	=(COUNT(A2:B2)=2)*(B2-A2+1)
C3	=IF(OR(A3="",B3=""),0,DATEDIF(A3,B3,"d")+1)
C4	=IF(OR(A4="",B4=""),"0",DATEDIF(A4,B4,"d")+1)

You do not want a text "0".

 

[TheAssetMgr]

My worksheet is set up such that column C=Date Out, D=Date In, E=Travel Days and F=Comp Days so I modified your code to read:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("E6")
    If Range("C6").Value = "" Or Range("D6").Value = "" Then
        Range("E6").Value = ""
    Else
        Range("E6").Value = (Range("D6").Value - Range("C6").Value) + 1
    End If
End With
End Sub

While the Travel Days cell did stay blank when there weren't any dates in the other two fields it doesn't calculate the Travel Days when there ARE dates in the two fields. Did I miss something in my interpretation of your direction to delete the formula in the Comp Days cell? My limited knowledge of excel macros makes me think they need to be kicked off by a Ctrl+X keystroke and that's not happening here. Is this sort of a passive macro that's running all the time?