Combining Year, Month, and Day Columns into YYYYMMDD Format

I am trying to do something that might be kind of simple, but I have not been able to do it.

I am trying to combine three columns representing Year - Column F, Month - Column D, and Day of Month -Column E into a single column G. I want to be able to sort this column. I would like to see it formatted as YYYYMMDD, with a single digit months and days being preceded by a zero. It could also be formatted as YYYY-MM-DD with the same single digit formatting requested. The cell format can be date, number, or text which ever you recommend.

I tried the formula in cell G2 =F2&D2&E2, but it did not give me a zero in front of the 2 for the month.

I am attaching a worksheet which shows what I am trying to accomplish. I want to fill in the cells highlighted in
yellow.

Thanks for any recommendations which can give me the results I would like to achieve.

I would like to have this column even if it is possible to sort Column A in date order, although I am not sure this is possible.

You can do this in G2 to include hyphens:

=F2&"-"&TEXT(D2,"00")&"-"&TEXT(E2,"00")

or if you don't want the hyphens you can do this:

=F2&TEXT(D2,"00")&TEXT(E2,"00")

The results are text values.

If you want numbers then you can do this:

=F2*10000 + D2*100 + E2

and format the cell as General.

Hope this helps.

Pete

Thanks this gives me exactly what I want in either text or number format.

I appreciate your help!

You're welcome - thanks for the rep.

Pete

Power Query

Please Login or Register  to view this content.