Solved

Tricky date & time formula

Posted on 2014-03-03
4
204 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 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 5 46
Left trim cells in column A Excel vba 2 32
vba autofilter in row 4 6 11
Problem to file 5 17
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

803 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