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

Convert Time to text in excel

Hi,

I am having a problem. I have the time as below (see picture)

2:30:22 PM

Now I want to conver this into JUST

14:30

in text format as prior data is in similar format (when I say prior data, the data available to me from a point before)

The prior data is entered on excel as this

'14:30 by a user...then the use of computerized scripts made the time look like above. But for reporting purposes, I need to make them look like old data. Thats the history of this.

Thanks for your time!
Temp.PNG
0
Shanan212
Asked:
Shanan212
  • 3
1 Solution
 
Alex_WCommented:
Have you tried right clicking the column containing times, click Format Cells..., choose Custom then hh:mm?
0
 
Shanan212Author Commented:
if I hit that, then the time comes as intended (14:30)

However, when I click the time/cell where the time is located

it shows "2:30:22 PM" on formula bar.

I want the core data to be changed though

Thanks!
0
 
Shanan212Author Commented:
I solved it by copying the time, pasting it in notepad, then copying from notepad to excel fields (pre-formatted to text)
0
 
byundtCommented:
Shanan212,
Given the formula you are using in your subsequent question, it doesn't matter whether the time is in text or time format.
=IF(COUNTA(A2:B2)<2,"",MOD(B2-A2,1))

For this reason, I suggest following Alex_W's suggestion of changing the format of that column to h:mm     This is simpler than taking a round trip through Notepad.

Brad
0
 
Shanan212Author Commented:
Solved
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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