We help IT Professionals succeed at work.

Splitting Time Value

Shanan212
Shanan212 asked
on
Medium Priority
292 Views
Last Modified: 2012-06-27
0950
0905
1302
0934
1044

Hi,

I have the above raw data. I want to split the time into "00:00" format.

When I enter "=Left(A2,2)&":"&right(a2,2)

I am getting 95:50

This means its not counting zero on the first cell.

Is there a go around for this? I couldn't find any on internet

Thanks!
Comment
Watch Question

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
Try a formula like:
=TEXT(A2,"00\:00")
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Try this version in B2

=TEXT(A2,"00\:00")+0

format B2 as a time value

regards, barry

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
Note that Brad's suggestion results in a text value, mine will give you a valid time value.....

barry
Muhammad Ahmad ImranDatabase Developer

Commented:
=--TEXT(A1, "00\:00")

Author

Commented:
Both worked!

I really dont need to format it as its being exported into a db.

Thanks!

Author

Commented:
Thanks Leoahmad as well (I suppose that would format it into number)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.