Hey guys,
I have to forecast a date of completion for a range of projects. I have got a formula to tell the reader that the day I completed a project is or is not in the quarter that I forecasted it to be completed in. But I would like to be able to tell the reader in the next column whether it was before or after the forecasted date.
To make that a little easier to understand I have uploaded an excel file with a simplified version of my spreadsheet.
In column K is my formula to say whether it was completed at the correct time. Basically if Column K says no then I want Column L to say before or after. I'm fine with it being left blank if column K says yes it was completed in the right time.
If I have not explained this well, I apologise. Please let me know if I have missed any information out.
Thank you.
LaylalyaL,
In cell L2, try this:
=IF(K2="No",IF(MATCH("Yes",B2:I2,0)>IF(J2<$C$1,1,CEILING(MATCH(J2,$B$1:$I$1,1),2)-1),"Before","After"),"")
And then copy down
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
It works until I put a 2010 date in, then it still thinks it is after but only is some cases.
See row 3 for the mistake.
I've worked out a simpler way, see spreadsheet 2.
Thank you for your help anyway
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks