Converting text and numbers to a calendar date

[FranAgrippina]

Good afternoon everyone,

I have a worksheet which uses both text and numbers to generate lot numbers for a product. However, I now need to also generate an expiry date 21 months and 30 months from the lot number date.

The problem lies in the fact the lot numbers are generated by the following:

Month: January (text format)
Day: 01 (custom formate - dd)
Year: 2012 (number formate - 0 decimal places) If I try to convert this cell to custom year format (yyyy) I get years in the 1900s - of course.

Each of these are in their own cell. I need them to come together in a format that would allow me to calculate the expiry date and then of course display it in the standard mm/dd/yyyy for the end user.

Can anyone give me a hand?

Thanks!

[Lemice]

Hello,

Assuming you have "January" on A1, you have 01 on B1, and 2012 on C1, you can try this formula
=DATE(C1,MONTH("1"&A1),B1)

[FranAgrippina]

Hello Lem,

Can you please break this formula down for me? I am having difficulty with the column/row designation in your formula and how they relate to the column and row you have the elements assigned. If it is helpful, my data sits in the sheet as follows: "January" = B3, "01" = B4 and "2012" = B5

Thank you

[Lemice]

In that case, try this one
=DATE(B5,MONTH("1"&B3),B4)

The syntax of DATE is DATE( year, month, Day ):
- Year value is in B5, right?
- Month value requires a little trick, MONTH("1"&B3) will return the number of the month, and the name of the month is in B3.
- Day value is in B4

Ugh, my eyes ...

[FranAgrippina]

Lem,

Thank you! It worked like a charm. Much simpler than what I was attempting to do.

Thanks again...my client will be very happy!

[Lemice]

Glad that I could help.

If you have found a fitting solution to your problem, please mark the Thread as [SOLVED] using the Thread tools right above post #1. It keeps things neat and tidy.

Have a great day.