Need to create date from 3 cells (1 with yr; 1 with month; 1 with day)

[Sleeplol]

Hi,

Is there a way to get a legitimate date from pulling the day, month, and year values from different cells??

For instance: Year in A2 and Month in B2 (Both A2 & B2 are drop downs), and C2 - AG2 are the days 1,2,3, etc

I'd like to have row C1 - AG1 display an actual excel date (or number) so that I can work in a holiday list via another formula.

However, my holiday list with is formula driven can only properly reference the cells C1 - AG1 if they are actual dates

 

[jasonb75]

Are the months entered as Jan, Feb, etc, or as 01, 02?

Also, what is your regional date format? The formula needs to be specific to your system settings. If I write it for UK dates then it will error with US dates. If you're using month names in a language other than English then that may need to be allowed for as well.

 

[Sleeplol]

Hi Jasonb75

The data is simply a value in each cell:

I'd like it to look like:

or even better:

 

[jasonb75]

You didn't answer the important bit.

what is your regional date format? The formula needs to be specific to your system settings. If I write it for UK dates then it will error with US dates.

 

[Sleeplol]

You didn't answer the important bit.

MM/DD/YYYY

I think that is what you're asking

 

[jasonb75]

Yep, that's what I was asking. Sorry, if I had been paying attention then I would have noticed it in the screen capture that you posted earlier

Enter this formula into C1 and drag right to get what you need.

=DATEVALUE($B2&" "&C2&" "&$A2)

It will most likely show the date serial number by default but you can change it to a proper date by formatting the cells if required.

 

[Sleeplol]

Hi Jasonb75,

Thanks for getting back, but I'm getting an error...

 

[jasonb75]

I can't actually test the formula because my regional settings are different but based on the information from you and the screen capture in post 3, it should work.

See if any of these work correctly.
=DATEVALUE(LEFT($B2,3)&"-"&C2&"-"&$A2)
=DATEVALUE($A2&"-"&LEFT($B2,3)&"-"&C2)
=DATEVALUE(C2&"-"&LEFT($B2,3)&"-"&$A2)

edit:-

Think the original needed another comma
=DATEVALUE($B2&" "&C2&", "&$A2)

 

[Sleeplol]

=DATEVALUE($B2&" "&C2&", "&$A2) Works Perfectly!!!!!

I've been puttering around with this way too long and you solved it in a snap.
Many many thanks!!