Help with looking up dates in reference to 'today'

[wolleh]

Hi guys - I didn't even know this forum existed! I can't wait to spend some time looking around, however I come to you with a cry for help..

I work on a mining camp and i'm trying to develop/rework a spreadsheet in order to save some time/labour.

Ok so this is a spreadsheet that is going to give my room cleaners their work for the day & do a few other things for me (not relevant at this stage).

The "next clean" column F needs to display when the next clean will be…
If today is the 4/3/14 then it needs to display 4/3/14, and if today is the 5th and their last clean was the 4th - it needs to show the next date which would be the 8th. And so on for the 3rd cleaning day the 11th..
Make sense?
In column G-I I've got a quick formula to populate the cleaning day cells depending if the room is occupied or not =IF(D2=1,E2+3,"") +7 & +10 for column H&I respectively.

I know I could just filter the cleaning days, but this will help in some other areas if I can get it to work.

This is a spreadsheet from another site that I've been given to re-work - it was previously all entered manually.

Any help is appreciated, i hope i have explained this clearly enough..
If you need any more info I'll be browsing all day
housekeeping.jpg

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[wolleh]

ok, workbook attached.

A friend has given me a formula which I have entered into F11, which seems to work!

I just stumbled upon another need though. I need to capture the rooms that are yet to check in, say tomorrow or the next day, so that we can clean them early.

I've added another couple of lines with a quick explination. Can we add to the formula in F11 to do this?

Thanks

Edit: Somehow ended up with 2 files attached. Use the bottom one please.

[wolleh]

Ok i think i got it. It all just kinda came together.

I edited my friends formula a little bit to give me what i wanted.

For anyone that can make sense of my ramblings, i used;

=IF(E14>TODAY(),E14,IF(E14=TODAY(),E14,IF(G14>TODAY(),G14,IF(H5>TODAY(),H5,IF(I14>TODAY(),I14,"")))))

File attached. See cell F14.Housekeeping - fixed.xlsm