• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populate dates based on IF condition between 2 columns

F

Frisco

New Member
Hi,

Sample below with Date3 column showing what the results should be


Date1 Date2 Date3


12/1/2012 1/1/2013 1/1/2013

empty 1/1/2013 1/1/2013

empty #NA empty

empty empty empty

12/1/2012 #NA 12/1/2012


Based on above example, how can I Populate Date3 based on the following criteria:

Date2 date overrides date in Date1

If there's a date in Date2, then populate the date in Date3

If there's a blank or #NA in Date2, then look in Date1

If there's a date in Date1, then populate date in Date3

If Date1 is blank, then blank in Date3

Date2 date overrides date in Date1


Thank you in advance.
 
Hi, Frisco!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Assuming that your data is in columns A:B starting from row 1 and that cells would only contain a valid date, #N/A or be blank, try this in column C:

=SI(ESNUMERO(B1);B1;SI(ESNUMERO(A1);A1;"")) -----> in english: =IF(ISNUMBER(B1),B1,IF(ISNUMBER(A1),A1,""))


Regards!

Regards!
 
Thank you SirJB7 for the quick response.


The results show the following:

[pre]
Code: 
Date1	Date2	Date3
empty	empty	[empty]
12/1/2012 1/1/2013 [empty]
empty	1/1/2013 1/1/2013
empty	#NA	1/1/2013
12/1/2012 #NA	[empty]
[/pre]
Your assumptions mostly correct, except our data starts on row2. The first row is for headers.
 
Hi, Frisco!

Not in my case. Give a look at this file, same input data as you, same formula as me:

https://dl.dropbox.com/u/60558749/Populate%20dates%20based%20on%20IF%20condition%20between%202%20columns%20%28for%20Frisco%20at%20chandoo.org%29.xlsx

Regards!
 
Awsome!

On your spreadsheet most everything works except for the last set

[pre]
Code: 
12/1/2012	#N/A	01/12/2012
[/pre]
The result should be 12/1/2012.
 
Oh no !


Priority should be Date2 but it appears Date1 gets populated

[pre]
Code: 
11/13/2012	11/06/2012	11/13/2012
11/5/2012	11/19/2012	11/5/2012
11/5/2012	10/31/2012	11/5/2012

11/26/2012		        11/26/2012
11/26/2012	11/13/2012	11/26/2012
11/13/2012		        11/13/2012
12/11/2012		        12/11/2012
11/13/2012	11/06/2012	11/13/2012
[/pre]
 
Hi, Frisco!

I don't know what you're doing but what you say in your penultimate post is what I see in the uploaded file. Here's a print screen where it does correctly get displayed that same example. If it doesn't work for you please upload your modified file.

https://dl.dropbox.com/u/60558749/Populate%20dates%20based%20on%20IF%20condition%20between%202%20columns%20%28for%20Frisco%20at%20chandoo.org%29.png

Regards!
 
Looks like I was looking for American date 12/1/2012 for Dec 1 when the result shows European date 12/1/2012 for Jan 12.


Regarding my data, will upload soon Thank you Sir JB7
 
Hi, Frisco!

It's not European date, it's actually American date too. Argentine is in America, isn't it?

Regards!
 
Hi, !

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: I'm afraid there's no such a feature, just topics & posts.
 
Hi SirJB7,

https://docs.google.com/file/d/0B7jxoehGdcTkUTFsT2NGNzlLWXM/edit

Can you explain why some dates are not populating in column Date3 when there are dates in column Date2? Thank you.
 
Hi, Frisco!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Populate%20dates%20based%20on%20IF%20condition%20between%202%20columns%20-%20PopulateDatesCondition%20%28for%20Frisco%20at%20chandoo.org%29.xlsx


It's an issue with your date format "m/d/yyyy", first I tried with my "dd/mm/yyyy" and it worked, then with "mm/dd/yyyy" and it worked too.


Regards!
 
WOW!!That is so "magical", especially the F2+Enter function.

That also seems to correct where Date2 value should supercede Date1 value.


Can we use F2+Enter for multiple cells at a time rather than 1 cell at at time? We have hundreds of empty cells to populate.

Again, Thank YOU !!.

[pre]
Code: 
Date1	   Date2	Date3
12/31/2012 10/17/2011	12/31/2012 (should be 10/17/2012)
[/pre]
 
Hi, Frisco!


It's me again, yes, how did you guess it? I have bad news for you: F2+Enter doesn't allow multiple cells usage, you can only profit from selecting a range or a group of ranges, and press F2+Enter successively for each cell so as to avoid selecting each next manually.


If you have a lot, but a lot!, of cells with that problem you can try this workaround at a helper column and then copy and paste values to original properly formatted range:

=FECHA(DERECHA(B26;4);EXTRAE(B26;4;2);IZQUIERDA(B26;2)) -----> in english: =DATE(RIGHT(B26,4),MID(B26,4,2),LEFT(B26,2))


Replace B26 as required.


Regards!
 
Hmm, still getting data from Date1 column (data from Date2 column should have priority).

[pre]
Code: 
Date1           Date2           Date3
8/31/2012	11/09/2012	8/31/2012	(should be 11/09/2012)
10/11/2012	01/09/2013	10/11/2012	(should be 01/09/2013)
[/pre]
 
Hi, Frisco!

Have you changed the whole related cells format to "mm/dd/yyyy" and updated its values with F2+Enter? Please check again this. If you don't succeed upload again the actual file indicating in separate columns the error description for two or three rows with the issue.

Regards!
 
You must log in or register to reply here.
Back
Top