Date/Time Format Issue

Fyrdawg

New Member
Joined
Aug 29, 2013
Messages
41
Hello.. I ran a report and it came out in the date/time (6/3/2020 4:32:04 PM) format and I cannot figure out how to change it to just time (4:32:04 PM). I have messed with the format cells and cannot figure out how to change it. The only way if figured out how to change the format, is to manually removed it from each cell, which will take me hours to fix the spreadsheet..

I'm sure there is a simple fix but I can't figure it out so any help is greatly appreciated.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,158
Office Version
  1. 365
Platform
  1. Windows
The image below should do what you want unless the current format is Text in which case we would need to do something else.
 

Attachments

  • Capture.JPG
    Capture.JPG
    18.3 KB · Views: 3

Fyrdawg

New Member
Joined
Aug 29, 2013
Messages
41
I am using the formula below and it doesn't count any of the times unless I remove the date. I also tried using the time function and custom function to h:mm:ss

=SUM(COUNTIF($D$3:$D$61,{">=6:00:00",">15:00:00"})*{1,-1})
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,158
Office Version
  1. 365
Platform
  1. Windows
Formatting only displays the manner in which you wish to see data. It does not change the underlying data which in this case includes a date value. If you wish to have only the time in a cell, then maybe a helper cell. Assume your data is in column A and you wish to show the time in column B. = A1 - Int(A1) which will give you the time value only.
 
Solution

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try this code

VBA Code:
Sub Timeformat()
'
' Timeformat Macro
'

'
    ActiveCell.FormulaR1C1 = "3:56 PM"
    Range("A2").Select
End Sub
 

pommesmitmayo

New Member
Joined
Jun 12, 2011
Messages
8

ADVERTISEMENT

Hello.. I ran a report and it came out in the date/time (6/3/2020 4:32:04 PM) format and I cannot figure out how to change it to just time (4:32:04 PM). I have messed with the format cells and cannot figure out how to change it. The only way if figured out how to change the format, is to manually removed it from each cell, which will take me hours to fix the spreadsheet..

I'm sure there is a simple fix but I can't figure it out so any help is greatly appreciated.
Quick question for you, are you at liberty to say what equipment you are using to generate said report. I'm using a Keysight multimeter and I have pretty much an indentical issue;
11/14/2020 10:42:00.061
I'd like to isolate the last three digits of the seconds too as over a thousand readings, (some of my testing lasts for over 3600 readings), at least three errors will be generated when I paste into another workbook.
 

pommesmitmayo

New Member
Joined
Jun 12, 2011
Messages
8
Ok, I've solved it for myself, in two steps. Not sure if it's possible to combine the steps but here is how, (supposing your data is in A1);
  • Using my exact data above, "11/14/2020 10:42:00.061", which consists of 10 characters, a space and then another 12 characters, enter the following formula into the cell you want the new value to appear in.....
  • =REPLACE(A1, 1, 11, ""), then enter the following formula into the cell you want the final value to appear in.....
  • =REPLACE(A5, 9, 4, "").
So the Replace function does exactly that, replaces text within a cell with what you want.
A1 in this case is the 'subject' cell.
1 in this case is the character where you want the replacing action to start from.
11 in this case is the number of characters you want to replace.
"" in this case is what you want the old characters replaced by, (in both our cases that will be nothing so just put the two quotes together).
The second formula of course just does it again.
 

Fyrdawg

New Member
Joined
Aug 29, 2013
Messages
41
Quick question for you, are you at liberty to say what equipment you are using to generate said report. I'm using a Keysight multimeter and I have pretty much an indentical issue;
11/14/2020 10:42:00.061
I'd like to isolate the last three digits of the seconds too as over a thousand readings, (some of my testing lasts for over 3600 readings), at least three errors will be generated when I paste into another workbook.
The report is generated from a database we are required to use and cannot change any of the settings nor customize the reports.
 

Fyrdawg

New Member
Joined
Aug 29, 2013
Messages
41
Formatting only displays the manner in which you wish to see data. It does not change the underlying data which in this case includes a date value. If you wish to have only the time in a cell, then maybe a helper cell. Assume your data is in column A and you wish to show the time in column B. = A1 - Int(A1) which will give you the time value only.
Thank you so much. This was a simple fix..
 

Watch MrExcel Video

Forum statistics

Threads
1,127,478
Messages
5,624,990
Members
416,065
Latest member
meiravmeron

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top