Log In
Register
Home
August 14, 2018
Offline
Good day everyone,
I am looking for some help please as I have to write an Excel formula that reads dates and I am battling. Mynda was kind enough to help me yesterday but unfortunately the formula was only partially successful so she has asked me to put it on the forum.
Column B Column J
| AccntNo | APPLICATION DATE | S | MAIL TITLE | Ini | INITIALS - 2 | Client Name | SURNAME - 2 | Category | URV date |
| 0031 | 2002 06 21 | 5 | MR/S | J | NT | MOW | MOW | LM | 2005 11 02 |
| 0053 | 2013 07 30 | 5 | MRS | JBE | JENKINS | OM | |||
| 0085 | 2004 04 13 | 6 | MR/S | LR | P | HILDITCH | HILDITCH | OM | |
| 0086 | 2004 04 30 | 2 | MR/S | JH | CL | WOODMAN | WOODMAN | OM | 2016 04 25 |
| 0090 | 2004 05 21 | 3 | MR/S | WG | JM | ANNANDALE | ANNANDALE | LM |
So I need a formula which reads the dates in columns B and J and that says :
If there is NO date in column J then it needs to enter the date from column B (highlighed in red), but if there is a date in column J then it needs to read that date and ignore the date in column B
Mynda suggested this formula :
=IF(NOT(ISBLANK(R2)),R2,C2)
which works perfectly for the columns where there is no date and it draws the date from column B which I need, but if I try to use it for the rows where there is a date in Column J then it stays blank.
I am really stressing about this as my boss is back on Thursday and I need to have this sorted by then. I have attached the actual spreadsheet so that you can see the rows and columns so that it's easier to visualise.
Any help would be very much appreciated please!
Thanks, Terri
See Answer
July 16, 2010
Offline
Hi Terri,
When you emailed me your original question you said the other dates were in columns C and R, but the image and your question above say they're in columns B and J, so all you need to do is change the formula so it references columns B and J like so:
=IF(NOT(ISBLANK(J2)),J2,B2)
In English it says if J2 is not blank/empty, then return J2, otherwise return B2.
Hope that works.
Mynda
August 14, 2018
Offline
Hi Mynda
So sorry I should have changed the rows and columns in your example as I simplified the spreadsheet so that it would fit in the forum page.
That formula works perfectly for the rows where there are is no figure in column J and it correctly pulls the date from column B, but if there is a figure in column J then the formula is blank.
Could it be because I am working with dates?
Could I email you my spreadsheet and then perhaps you could use it for training?? Or perhaps it is the formatting on the formula cell, although I have tried it as "general" and "date" formatting?
Thanks
Terri
August 14, 2018
Offline
This is the spreadsheet!
VIP
Trusted Members
December 7, 2016
Offline
Hello Terri,
You are not using the same formula in column S. For example:
In cell S7 you have =IF(NOT(ISBLANK(R7)),R7,C7)
In cell S8 you have =IF(NOT(ISBLANK(R8)),R8,C8)
In cell S9 and later you have =IF(NOT(ISBLANK(R9)),C9,R9), you have changed the order for what data to pull.
You need to use the formula you have in cells S7 and S8 for the whole S column. The reason to why you don't get the result you want is because the empty cells in R column are not empty. Click on any "empty" cell in R column, press delete key and you will see the date from C column being pulled to the S column.
Br,
Anders
Answers Post
August 14, 2018
Offline
Thank you so much Anders, column R looked empty to me but as soon as I deleted what was in that row the formula worked perfectly!! I'm so happy as I can easily sort the spreadsheet to have all those "blank" rows together, delete them and then use the formula.
Sorry I should have deleted the different formulas as that was me trying to play with Myanda's original formula that she gave me to see if it made any difference to the results, when what I should have been doing was just hitting delete 🙂
Thanks to everyone who helped me! I'm off now to go and try it
1 Guest(s)
