Number of days between dates

[ConfusedMum]

Hi all,

I am trying to count the number of days between two days (count to include both the start date AND the end date); however, to complicate things, it might be that the end date cell has a date in it, but the start date is showing as text or that the start date is fine, but that the end date is blank.

So far I have got the formula =IFERROR(DAYS(S2,G2), "Not fully complete")
- where S2 is the end date and G2 is the start date
- however, a few of the G2 cells have the wording in to show they need checking - so text, rather than the date
- and, a few of the S2 cells are blank not having an end date put in as yet

What I am trying to look for is a formula that looks at both S2 and G2 and if S2 is blank OR G2 is text then it returns "Not fully complete". At the moment it is working where G2 is text, but where S2 is blank I am getting -45078 for some reason!!

Any ideas please oh clever Excel minds!

Thanks so much, Cathy

 

[awoohaw]

try:

=IF(OR(isnumber(S2)=FALSE,Isnumber(G2)=FALSE),"Not Fully Complete",DAYS(S2,G2))

 

[awoohaw]

or:

=IF(OR(S2="",G2=""),"Not Fully Complete",DAYS(S2,G2))

 

[ConfusedMum]

or:

=IF(OR(S2="",G2=""),"Not Fully Complete",DAYS(S2,G2))

Oh my goodness - you are an Excel God!!!
Thank you SO much that worked perfectly! Thank you, thank you!

 

[awoohaw]

I'm pleased you found a solution in the forum. And welcome to the Mr. Excel Forum.
Thanks for the feedback, and best wishes!

 

[Dave Patton]

You stated "(count to include both the start date AND the end date)"

Please review Excel's help information for the Days function.
It counts the days between 2 dates.
What result do you require for the following?
I included an alternative that you can try.

T202306a.xlsm
	G	H	I	J									S
1		Days
2	1-Jan-23	4	5										5-Jan-23
3	1-Jan-23	30	31										31-Jan-23
4	1-Jan-23	364	365										31-Dec-23
5
3b

Cell Formulas
Range	Formula
H2:H4	H2	=IF(OR(ISNUMBER(S2)=FALSE,ISNUMBER(G2)=FALSE),"Not Fully Complete",DAYS(S2,G2))
I2:I4	I2	=IF(COUNT(G2,S2)=2,S2-G2+1,"Not Fully Complete")