It seems like I keep falling into the trap of thinking I've got dates & times formulas figured out in Excel but then a new scenario comes along and I find myself completely confused once again. :P
For example, in the following screenshot, each value in Columns B-G (blue font) was made manually. The goal is to come up with a formula in Column I which combines the values in the preceding columns into an entry with the format shown.
• All cells in the spreadsheet have General formatting.
• This includes the "Time" column. In other words, Col E is NOT formatted to military or any other time format. However, values in the thousands & hundreds columns represent hours and values in the tens & ones columns represent minutes (i.e. #hmm) with AM/PM signified by the single
character in Col F
• The general desired format for Col I is:
yyyymmmdd(ddd)hhmma_text… (for AM times)
yyyymmmdd(ddd)hhmmp_text… (for PM times)
• At first glance, a simple concatenation formula seemed as though it would suffice. However, numeric months must be changed to 3-letter months and, the real curveball, days must be converted to the correct 3-letter day for the right month in the right year.
I'm looking forward to the solution.