Morning Team, just a quick excel tester this morning….

I am putting together a sheet relating to bookings and programming when certain things associated with said booking need to be done….

Column D Can either be blank / empty or have a date in if booked.
Column I needs to replicate that date (or have an empty / blank cell if no date in Column D)
Column G will need the cell in Column I less 7 days (if it contains a date) or return a blank if no date is yet entered in Column D.

Obviously its easy enough to do using the If function, however as everything is done in date format a blank cell defaults to 01-Jan-1900.

I have tried different Date formats but I cannot get a zero / blank cell to return where I need it.

Solution required please…….

Conditional formatting?

Set to change format to number when zero?

Just a guess, not sure if it’s possible.

In column I: =if(d1<>0,d1,””)
In column G: =iferror(i1-7,””)

^^^what he said…

=if(i1=0,””,i1-7)
?

Only obviously d not i

All working using the rather nifty “iferror” function.

Reward yourselves with a nice cup of tea, maybe a biscuit.

Thanks, maybe I will!

Now… why isn’t this doing what I want?

=IF(BE33>1,0,-IF(D34>0,MAX(A34,0),0)+IF(AND(IFERROR(VLOOKUP(C34-$V$18,$Z$10:$AF$15,1,0),0)=C34-$V$18,BE33=1)=TRUE,-MAX(A34,0),0))

“I have tried different Date formats but I cannot get a zero / blank cell to return where I need it.“

I think this should do it, custom number format:

dd-mmm-yy;;

I think the main problem you’ve run into is that Excel can’t format a negative date, so you end up with ######### instead. It’s not strictly an error. The formatting above will simply hide it.

njee20 – Member
Thanks, maybe I will!

Now… why isn’t this doing what I want?

=IF(BE33>1,0,-IF(D34>0,MAX(A34,0),0)+IF(AND(IFERROR(VLOOKUP(C34-$V$18,$Z$10:$AF$15,1,0),0)=C34-$V$18,BE33=1)=TRUE,-MAX(A34,0),0))

Because that’s no way to program a Bluetooth kettle, obviously

Also you seem to have an extra clause in your vlookup from what i can read, and you look to be returning column 1 in the range, which seems odd.

I was kidding really, it does actually work, although wasn’t my formula, I’m sure it can be cleaned up!