Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Tricky date & time formula

Hello,

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.

aNotes:

• 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.

Thanks
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
  • 2
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try

=A2&TEXT(DATE(A2,B2,1),"MMM")&TEXT(C2,"00")&"("&TEXT(DATE(A2,B2,C2),"ddd")&")"&TEXT(D2,"0000")&E2&"_"&F2

Open in new window


Regards
0
 
WeThotUWasAToadAuthor Commented:
Thanks for the response.

Your cell references are not exactly correct but you helped me get the formula:

=B3&
TEXT(DATE(B3,C3,D3),"mmm")&
TEXT(DATE(B3,C3,D3),"dd")&
    "("&
TEXT(DATE(B3,C3,D3),"ddd")&
    ")"&
TEXT(E3,"0000")&
F3&
    "_"&
G3
0
 
barry houdiniCommented:
You could probably combine the first 3 TEXT functions - I got the same result with this formula in I3

=TEXT(DATE(B3,C3,D3),"yyyymmmdd(ddd)")&TEXT(E3,"0000")&F3&"_"&G3

regards, barry
0
 
WeThotUWasAToadAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now