Custom time format to save time inputting data

**daveroe** · 11-02-2009, 11:51 AM

I was wondering if anyone could offer suggestions to help same time on data entry. I'm trying to set up a cycle time spreadsheet where I can two times and get the difference between the times. My problem is that I have hundreds of times to enter and right now I have to type it as the hour, the colon and then the minutes. I can custom format the time as "0/:00" and only have to type in the digits of the time but Excel doesn't recognize this format as time base on 60 minutes, it recognizes it as just a number so when I try to get the difference between two times say 7:50 - 7:45 it returns the correct answer = 0:05 but if it the time spans across the hour say 8:05 - 7:55 it returns the answer = 0:50 instead of =0:10. A workaround is to go to each instance where this occurs and subtract 40 from the answer to get the correct time but this to is time consuming.
Sorry for the verbose questions but any help would be greatly appreciated.

Thanks,

Dave

**DonkeyOte** · 11-02-2009, 11:54 AM

Either

a) use VBA worksheet_change event to modify cell values upon initial manual entry (ie convert cell value to true time format)

b) use formulae in adjacent cells to translate the manual entry to time format and work with those, eg:

B1: =0+TEXT(A1,"00\:00")
would be time equiv. of A1 were A1 entered as say 1745
cell to be formatted using Time format

c) adapt your formulae to use existing non-time entries, eg

C1: =TEXT(A2,"00\:00")-TEXT(A1,"00\:00")
would generate a valid time difference between A2 & A1 where both were entered in non-time format - eg 805 and 755 respectively.
cell to be formatted using Time format

**daveroe** · 11-02-2009, 12:10 PM

Excellent! I chose option B and it worked brilliantly although I'm not quite sure how it works yet but it does.

Thanks!

**DonkeyOte** · 11-02-2009, 12:15 PM

The TEXT function is in essence just replicating what you're doing yourself in your custom format - ie taking a non-time value and inserting a : between 2nd & 3rd digits, so if

A1: 1745

B1: =TEXT(A1,"00:\00") --> "17:45"

however you need the above as an actual time value rather than a text string and to convert number stored as text to number we coerce it... (times are numerical values)

B1: =0+"17:45"

will become 17:45 in time format or say 0.739583 in General terms (shown to 6 decimals) given 1 = 24 hours.

(obviously all of the above is dependent upon entering minutes with leading zeroes where required - ie 805 rather than 85 for 8 hours 5 minutes)