Working with time formats

Goodspeed

New Member
Joined
Sep 24, 2006
Messages
32
Hello,

I am trying to find a way to either import my data, preformat cells or reformat cells in order to display the 0 that would display in the "hour" area of a time display when that time is less than 1 hour. I import quite a bit of "time" data into excel and it is often in increments that are less than a hour, for example; :37:15 = thirty seven minutes and fifteen seconds. When I want to sum these types of entries though excel interprets the total as a time which is not what I need. I find though that when I add the 0 in the hour area of the time it does total the times correctly. For example 0:37:15 + 0:24:07 = 1:01:22 whereas if I try to add :37:15 + :24:07 = I get 0:00:00 as if excel doesn't know what to do with it? Does anyone have any ideas on how I could overcome this?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

macajm

New Member
Joined
Oct 21, 2005
Messages
32
you could create a macro or run a second column that uses the formula like:-
=IF(LEFT(A1)=":",VALUE("0"&A1),A1)
 

Goodspeed

New Member
Joined
Sep 24, 2006
Messages
32
Thank you for your advice. I was able to use your suggestion to solve my problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top