Formula Returning FALSE instead of date

[MrCameronExcel]

Hi,

I am using a formula to come up with a date based on cells above.

The formula in cell N5 is below. The formula should return +0.5 days after the last workday from 2 rows above (either in column S or U), while excluding holidays and weekends.

This same formula worked in N4. For some reason it is returning a FALSE and not a date in N5. It should be returning 18/01/23.


=IF(U3="",IF(WEEKDAY(S3+0.5)=7,WORKDAY(S3,1,Holidays!$A$5:$BS$5),IF(IF(ISNUMBER(MATCH(S3+0.5,Holidays!$A$5:$BS$5,0)),1,0)=1,WORKDAY(S3,1,Holidays!$A$5:$BS$5),IF(CELLISDATE(S3),S3+0.5,

IF(WEEKDAY(U3+0.5)=7,WORKDAY(U3,1,Holidays!$A$5:$BS$5),IF(IF(ISNUMBER(MATCH(U3+0.5,Holidays!$A$5:$BS$5,0)),1,0)=1,WORKDAY(U3,1,Holidays!$A$5:$BS$5),U3+0.5))))))

	N	O	P	Q	R	S	T	U
1	Large Lounge	Small Lounge	Bedroom 1	Bedroom 2	Bedroom 3	Kitchen	WC	Bathroom
2	16/01/23	16/01/23	17/01/23	17/01/23	18/01/23	18/01/23
3	16/01/23	16/01/23	17/01/23			17/01/23		18/01/23
4	19/01/23					19/01/23		20/01/23
5	FALSE	#Value!	#Value!	#Value!		#Value!	#Value!	#Value!

Any ideas?

Thanks for looking

 

[MrCameronExcel]

SOLVED.

I Just needed to close of the second IF statement prior to the second line.

=IF(U3="",IF(WEEKDAY(S3+0.5)=7,WORKDAY(S3,1,Holidays!$A$5:$BS$5),IF(IF(ISNUMBER(MATCH(S3+0.5,Holidays!$A$5:$BS$5,0)),1,0)=1,WORKDAY(S3,1,Holidays!$A$5:$BS$5),IF(CELLISDATE(S3),S3+0.5,))),

IF(WEEKDAY(U3+0.5)=7,WORKDAY(U3,1,Holidays!$A$5:$BS$5),IF(IF(ISNUMBER(MATCH(U3+0.5,Holidays!$A$5:$BS$5,0)),1,0)=1,WORKDAY(U3,1,Holidays!$A$5:$BS$5),U3+0.5)))

 

[Alex Blakenburg]

Just curious, do you have a UDF for CELLISDATE ? It is not an actual Excel function and gives a #NAME? error on my machine.
You have MS365 and I think you could simplify the function using the LET statement but you don't seem to have an option for the FALSE component of "IF(CELLISDATE(S3)"

Your formula formatted looks like this:

=IF(U3="",
        IF(WEEKDAY(S3+0.5)=7,
            WORKDAY(S3,1,Holidays!$A$5:$BS$5),
            IF( IF( ISNUMBER(MATCH(S3+0.5,Holidays!$A$5:$BS$5,0)),1,0)=1,
                  WORKDAY(S3,1,Holidays!$A$5:$BS$5),
                  IF(CELLISDATE(S3),S3+0.5,))),
         IF(WEEKDAY(U3+0.5)=7,
             WORKDAY(U3,1,Holidays!$A$5:$BS$5),
             IF( IF( ISNUMBER(MATCH(U3+0.5,Holidays!$A$5:$BS$5,0)),1,0)=1,
                  WORKDAY(U3,1,Holidays!$A$5:$BS$5),
                  U3+0.5)))