Combining ISBLANK and NOT(ISBLANK() to calculate dates..(or other suggestions)

[vertabatim]

Hello,

For a document at work I need to calculate the total days that we have had someone stay at our property. Something that should look like below.

Room	Room (per person)	Date in	Planned Date Out	Actual Date Out	Total Nights
1511	1511	25/09/2020	9/10/2020		7
	1511	25/09/2020	9/10/2020
302	302	23/09/2020	07/10/2020	24/09/2020	1
2121	2121	10/09/2020	24/09/2020	07/10/2020	14
	2121	10/09/2020	24/09/2020	07/10/2020

So my "logic" to calculate is
IF A1 is not blank AND E1 is Blank then DATEDIF(c1,now(),"d")
Otherwise
IF A1 is not blank and E1 is not blank then DATEDIF(c1,e1(),"d)

I am really struggling to get my head around the multiple isblank or not(isblank) functions.

Current work in progress is

=IFERROR(OR(IF(ISBLANK(A1),"",(DATEDIF(A1,E1,"d"))),""),(IFERROR(IF(ISBLANK(A1),"",(DATEDIF(C21,NOW(),"d"))),"")))

Hoping there is a much easier way to do this...

Hopefully this makes enough sense as my brain is slowly losing function the more I stare at this.

Regards,

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E	F
1	Room	Room (per person)	Date in	Planned Date Out	Actual Date Out	Total Nights
2	1511	1511	25/09/2020	09/10/2020		7
3		1511	25/09/2020	09/10/2020
4	302	302	23/09/2020	07/10/2020	24/09/2020	1
5	2121	2121	10/09/2020	24/09/2020	07/10/2020	27
6		2121	10/09/2020	24/09/2020	07/10/2020
Data

Cell Formulas
Range	Formula
F2:F6	F2	=IF(A2="","",IF(E2="",TODAY()-C2,E2-C2))

 

[CA_Punit]

Try This

=IF(A2="","",IF((A2<>"")*(E2<>""),DATEDIF(C2,E2,"d"),DATEDIF(C2,NOW(),"d")))

But how did you get 14 in row 4..

 

[vertabatim]

Hello and thank you,

Fluff it looks like that has done the trick for me, I have been making it progressively more complicated for my self so it was nice to see it simple.

And thanks CA_Punit, and you are correct that was my bad should look more like the below.

Room	Room (per person)	Date in	Planned Date Out	Actual Date Out	Total Nights
1511	1511	25/09/2020	9/10/2020		7
	1511	25/09/2020	9/10/2020
302	302	23/09/2020	07/10/2020	24/09/2020	1
2121	2121	10/09/2020	24/09/2020	24/09/2020	14
	2121	10/09/2020	24/09/2020	24/09/2020

Regards,

 

[Fluff]

Glad we could help & thanks for the feedback.