MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
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]I have tried different Date formats but I cannot get a zero / blank cell to return where I need it.[/i]"
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!