Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Tricky date & time formula

Posted on 2014-03-03
4
Medium Priority
?
210 Views
Last Modified: 2014-03-09
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
Comment
Question by:WeThotUWasAToad
  • 2
4 Comments
 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 2000 total points
ID: 39902485
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
 

Accepted Solution

by:
WeThotUWasAToad earned 0 total points
ID: 39903415
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 39903572
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
 

Author Closing Comment

by:WeThotUWasAToad
ID: 39915668
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question