Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Tricky date & time formula

Posted on 2014-03-03
4
Medium Priority
?
209 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 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

671 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