Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Convert MMM DD, YYYY hh:mm:ss to Short date in excel 2007

I have an Excel report that I import from IBM Clear Quest tool (Web App). A field, 'Submit_date' in this report has data in the following format:

'Jan 12, 2012 12:00:00 AM'

If I double-click in the cell the cursor is after the AM. When I click outside of the cell, the date time stamp changes to 1/12/2012.

My question is, how to change the entire column so that all data gets converted to short date (mm/dd/yyyy) and thereby making it a data sortable field.

Appreciate your help very much!


Post your answer or comment

comments powered by Disqus
I receive data from a database query in the format of YYYYMMDDHHMMSS, and I'd like to be able to convert it to MM/DD/YYYY HH:MM:SS (dummy 2003 spreadsheet attached).

I searched and found this thread which shows how to convert dates, but I'd like to know if it's possible to modify the formula to include the time...

Thanks in advance for any assistance!

I have created a field to keep track of every date/time a record was posted. I have created a variable sysdate and made that = Date.

When I saved

Code:
Dim sysdate as variant
 
'when I copy the records over, the code for sysdate is...
 
Cells(nextrow,3) = format(sysdate, "mmm-dd-yy hh:mm:ss am/pm")
When i check the actual cell, the date is correct but the time is always 12:00:00.

Does someone know how to rectify this?

Thx

I'm hoping someone with some expertise can assist me,

I have Excell 2007 and am having challenges with the date and time showing in the correct format.

I've tried highlighting the column with the dates, then go to Date -> More Number formats and on the number tab selecting Date, Type 14/3/2001 13:30 with the locale (location) Hebrew (Israel) and then click OK. This has not worked for any days numbered 1-12. These revert to the MM/DD/YYYY H:MM:SS AM format

I've also tried hightlighting the column with the dates, then go to the Data Tab -> Text to Columns -> Delimited -> Tab -> on the Column data format clicking Date and choosing DMY and then Finish. This also has not worked for any days numbered 1-12. These revert to the MM/DD/YYYY H:MM:SS AM format

Thank you

Everyone,

My first time here so I hope I detail my issue correctly.

I have a spreadsheet which has a date column and the dates are in the format MM/DD/YY HH:MM:SS.

I need to group the corresponding values in the the other columns based on 'hour of day' . In other words...

9/21/2011 14:11:45
9/21/2011 14:32:11
9/21/2011 17:01:03

should read as

9/21/2011 14:00
9/21/2011 14:00
9/21/2011 17:00

Is this possible?

Can anybody tell me how to convert a text field received as mm/dd/yy hh:mm:ss AM/PM to dd/mm/yyyy hh:mm:ss. I have to convert the AM/PM and express the time in 24h format.

An example:

Text field shows 05/04/07 03:25:00 PM (May 4th) and I want to have a time and date field, that I can use for calculation, as 04/05/2007 15:25:00

Many thanks.

Hi all,

The macro below deletes all duplicate rows by checking Column A for duplicate entries. It works fine for ordinary numbers but it wont work for for times and dates (i.e for data in this format 01/03/2011 07:52:04). All entries in column A are in the format dd/mm/yyyy hh:mm:ss. How to I modify the code to do this. I tried declaring the variable x As date but it doesn't work. Any ideas please?

Sub DeleteDups()

Dim x As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x

End Sub

Hi everyone,

I am struggling to get the difference between two dates that are in the format of DD/MM/YYYY HH:MM:SS AM/PM.

On one column for example I have the date:
9/10/2008 10:18:00 AM

On one column for example I have the date:
12/10/2009 11:48:00 PM

Ideally I would like the date to be displayed as
"3 days, 01:30 mins" in one column.

I would also like another column to give me the break down in hours.
ie "73.5hrs"

Can anyone assist or point me in the right direction?

Greetings,

I have attached a file with this post. I need help in converting hh:mm:ss to mm:ss and into seconds. The issue is when I extracted data it is appearing as 0:mm:ss. Most of the data is in minutes and seconds so why does it show as 12:mm:ss when it should be 00:mm:ss.

So how should I convert this data first to just mm:ss and then into seconds. I am calculating average time and hence i need the data. I tried using a mod function but I was not completely successful.

I found couple of answers by others in other posts but those formulas are not working.

Regards

Hi everyone. As above, what would be the formula to convert HH:MM:SS to Seconds?
If it can be done.

Thanks for any replies in advance

P

Is there a way that you can either format or write a formula that will convert hh:mm:ss to just minutes?

Thanks for your help.

Squeakums

Hi,

I have a Excel file with time values in a number of columns. The times are
in hh:mm (04:03) format but should be in mm:ss (04:03) format.

In other words, whenthe times were first entered they were put in as 04:03
and formatted as hh:mm. They should have been formated as mm:ss

When I try and change the format to mm:ss I get 03:00, I want 04:03

I hope this makes sence.

Thanks for your help.

Killian

Hi All,

I wonder if anyone can give me assistance with a problem I'm having converting a value of time that is stored in a cell in the format hh:mm:ss to seconds.

I've tried the obvious and had no joy.

Any tips gratefully accepted.

Thanks

K

Hey, so I'm trying to input an "x" in the far left column. Everytime I put an "x" in that box, I want the time NOW (with hh:mm:ss) to show up in the Column labeled "time started" for the corresponding row.

I think I have the coding right:

Sub NOWTIME()
On Error Resume Next
With Selection
.Value = Now
.NumberFormat = "hh:mm:ss"
End With
If Err.Number <> 0 Then
Beep
End If
End Sub
But can't seem to figure out how to use the "x".

Thanks for your help in advance, it is much needed.

How can I convert minutes:seconds (mm:ss) to just seconds in excel?

E.g. 01:00 to 60seconds, 02:00 to 120 seconds?

Your help would be greatly appreciated.

Cheers

how do I attach just one single sheet to an email in Excel 2007. I have been
trying to do this only it only lets me add as the 'body' of an email or it
will attach the whole workbook, not just one sheet.. Help!

How can I add the OneNote 2007 icon to the ribbon in Excel 2007? The icon is
in the ribbon in Outlook 2007 but none of the other functions.

I have created Excel inport and the data is without the first quote symbol in the grid. But if I select the grid, I can see the first quote symbol appears in the Excel. How to show grid in Excel 2007 as I have clicked Tab menu View and there is some Show/hide area for grid. Is this or I have to choose other as I still do not see the first symbol.

Sir, Can you help me convert hh:mm:ss into hours,

that is 90:10:20 to 90.1722

Thank you for your support

Shandow

I have a cell with hh:mm:ss formatted values of say 2:02:48, but need to run some calculations. I want to convert this to the just the number of seconds .. in this case 7368 seconds, but can't see how to do it. Changing the format of the cell to just ss returns 48.

I'm sure this is really simple and I'm being a bit thick.

I am inputting two times as h:mm:ss and subtracting them. I am trying to
get the final answer as seconds. The only thing I can think of is trying to
use LEFT and RIGHT to separate hh, mm and ss, multiply to convert the hh and
mm to seconds, and then add to get total seconds.

One - the LEFT and RIGHT don't seem to want to work for me - I keep getting
decimal answers, even with a hh:mm:ss format.

Two - is there an easier way?

Ed

Does anyone know how to convert 357646 seconds into d/hh:mm:ss format.
ALso does anyone know how to find the different between date and time. e.g
different between 01/02/2005 10:50:14 and 02/02/2005 16:47:08?

Is there a way I can use a time entered in a cell in the format hh:mm:ss and
convert it into a decimal of minutes?

I have tried using the 'Time' function but can only get it to work if I
enter each of the three parts in separate columns.

Thanks

Noel

Hi,

I hope you can help me.I have a lot of raw data were the date and time column is spread over two columnsand the time appears in a very funny decimal format. See below:

Column A Column B
08/09/2007 600.4
08/09/2007 1232.23
14/09/2007 1914
11/09/2007 2519.09

I have given four different examples above and I need to convert these fields into a single time/date stamp (dd/hh/yyyy hh:mm).

So the above example should come in one field as
08/09/2007 06:04
08/09/2007 12:32
14/09/2007 19:14
12/09/2007 01:19

My raw data consists of more than 15.000 records and I am unable to convert all of these numbers manually. Can you help me?

Also, please note the last example. My times often exceed the normal 24hour spread but includes up to 30 hours at time which needs to be added to the next day.

Hope this makes sense.

I appreciate all the help you can offer!

Thank you.
Kris

Hi,

I have time in the below format in a cell and convert the time into HH:MM:SS format. Can you please let me know how I can do it? Please help me with this.

Format:

7H 10S convert to --> 7:00:10
10H 30M 05S convert to --> 10:30:05


No luck finding an answer? You could always try Google.