Solved

Tricky date & time formula

Posted on 2014-03-03
4
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 52

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 the scrolling table in Microsoft Excel using the INDEX function.

632 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