Date/Time Format Issue

[Fyrdawg]

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.

 

[alansidman]

The image below should do what you want unless the current format is Text in which case we would need to do something else.

 

[Fyrdawg]

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]

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.

 

[pedro-egoli]

Try this code

Sub Timeformat()
'
' Timeformat Macro
'

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

 

[pedro-egoli]

Try this code

Sub Timeformat()
'
' Timeformat Macro
'

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

Sorry forget this post

 

[pommesmitmayo]

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]

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]

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]

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..