• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Excel - Merge date time

How can I merge the date and time into a single cell and have it formatted as mm-dd-yyyy hh:mm:ss? I am attaching the excel spreadsheet. I know that I can use a formula, however I was not able to get it to work. I have not used excel formulas before. CITY-OF-OCALA-11211-1-.xlsx
0
qbjgqbjg
Asked:
qbjgqbjg
  • 5
  • 5
5 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
Add the two together and format as desired.

=B8+C8

See attached.

Kevin
CITY-OF-OCALA-11211-1-.xlsx
0
 
qbjgqbjgConsultantAuthor Commented:
When I used =B8+C8, I only got the date. Also, how do you format as mm-dd-yyyy hh:mm:ss?
0
 
zorvek (Kevin Jones)ConsultantCommented:
Adding the two together give you a date time value. It has to be formatted to display the hours, minutes, and seconds. See the formatting in the sample I uploaded. The format is "m/d/yyyy h:mm:ss".

Kevin
0
Industry Leaders: 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!

 
qbjgqbjgConsultantAuthor Commented:
Under the format I selected custom, butI don't see the format that you have. I don't see "m/d/yyyy h:mm:ss". There is "m/d/yyyy h:mm".



0
 
zorvek (Kevin Jones)ConsultantCommented:
You have to enter it manually. The custom formats that are displayed are previous ones that you entered.

Kevin
0
 
qbjgqbjgConsultantAuthor Commented:
One more question. Once you have the merged column, can you get rid of the original columns or do they have to stay?
0
 
zorvek (Kevin Jones)ConsultantCommented:
You can delete the original columns but you first have to copy and paste as values the new column data because it contains a formula referencing the other columns to be deleted.

Kevin
0
 
qbjgqbjgConsultantAuthor Commented:
How do you paste the values. When I tried it, it copied it as a formula same as the Column I copied from.
0
 
zorvek (Kevin Jones)ConsultantCommented:
To convert one or more formulas to values in Excel 2003 and earlier, select the cells containing the formulas and press CTRL+C. Choose the menu command Edit->Paste Special, select Values, and click OK. Use the same technique to copy formulas as values to any other range.

In Excel 2007 and later, press CTRL+C, navigate to the Home tab and select "Paste Values" from the "Paste" menu in the "Clipboard" group.

To convert a single cell to a value (replace the formula with the formula result), press F9 instead of Enter when entering the formula.

Kevin
0
 
qbjgqbjgConsultantAuthor Commented:
Thanks, I appologize for my lack of knowledge, but you gave me exactly what I needed. Thanks again.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now