Hi,
Please see attached.
I'm simply trying to highlight dates that are in the past and those that are within 2 weeks of today. I would like it to then leave dates further into the feature unformatted and blanks blank.
Can someone please help - it doesnt do either of the last two conditions and, even worse, I can't get it to fill/copy down relative to the dates in its column i.e. all are conditioning for H2!
Very frustrated.
Please help urgently
ManBook2.xlsxy thanks
Last edited by ld2x07; 10-20-2014 at 09:47 AM.
Try using:Formula:
Please Login or Register to view this content.
Not that, as your range starts in row 1, your formula should also refer to row 1
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hello,
I have used this formula in column h and its working
=AND(H5<=TODAY(),H5>=TODAY()-14)
Regards,
Suhas
Hi all,
Thanks for the replies.
TMS your formula works well thank you.
I'm still having a problem copying and pasting the formula - when I paste the formatting from H5 to the other cells they all still reference H5 for the conditions. As suggested I changed the reference from H1 to H5 but this didn't help I don't think..
Is it because one of my formulas isn't a 'formula' but 'cell values between [=today()] and [=today()+14]' ??
Last edited by ld2x07; 10-20-2014 at 08:02 AM.
No changing it to =AND(H5-TODAY()>=0,H5-TODAY()<=14) still works but didn't help with the copying..
I don't have any $ in there, why won't it change??
Where you have 'cell values between [=today()] and [=today()+14]', it shouldn't matter.
However, where you have cell references, they should always refer to the first row used.
It's usually easier to adjust the range where the CF is used, than copying ... UNLESS you select the very first cell in the range and copy the CF from there to the other cells.
Regards, TMS
Sorted! Can't believe I was playing with this formatting for over two hours. I didn't realise it doesn't matter that it doesn't change. Every one of them keeps the references of the first cell in the column but for some reason work perfectly well! Baffles me but at least it works.
You're welcome.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks