?
Solved

Combine Date field from 2 seperate fields

Posted on 2011-10-10
8
Medium Priority
?
805 Views
Last Modified: 2012-06-27
Hello I have 3 columns in Excel where one had Date in the format mm\dd\yy and 2nd column is Hour and third column is MInutes and I want to have a column which will combine all thses three values in the format mm\dd\yy hh:mm kindly help?
0
Comment
Question by:welcome 123
8 Comments
 
LVL 59

Assisted Solution

by:Saurabh Singh Teotia
Saurabh Singh Teotia earned 240 total points
ID: 36943876

Use this formula assuming you have values in A1 as in date and time in b1..

=TEXT(a1,"mm/dd/yy") & " " & text(b1,"hh:mm")

Saurabh...
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 36943892

Alternatively you can also do this...

=a1+b1

and format the same as mm\dd\yy hh:mm by right click format cells-->custom and enter your format there..
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 260 total points
ID: 36943964
If date is in A2, hours (as a number) in B2 and minutes (also as a number) in C2 then in D2 try

=A2+(B2+C2/60)/24

format D2 in required format

regards, barry

PS Hey Saurabh!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:barry houdini
ID: 36943988
See example of the above

barry
27389135.xls
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 36944003


Hi Barry, How ru doing buddy..?
0
 
LVL 9

Expert Comment

by:experts1
ID: 36944223
Assuming your 3 columns are A2(mm/dd/yy), B2(hh)  and C2(mm),
then formula to concatenate all three is as follows:

=TEXT(A2,"mm/dd/yyyy" )&" "& RIGHT("00" & B2,2) & ":" & RIGHT("00" & C2,2)

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36944707
Welcome back, Saurabh!
0
 

Author Comment

by:welcome 123
ID: 36949013
Well I tried one which really worked well but did not ttu others but I always belive ingiving points to the first come solution, so will try them and then close and give points and the one i Tried and is working is from barryhoudini: =A2+(B2+C2/60)/24

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

840 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