Converting 4 digit numbers to time format in Excel

Question

This was marked as off topic - I hope that this much more long winded question will bring it back on topic. I have left the original question below for reference.

Original question:

I am importing a large text file into excel, this file consists of hundreds of times in the format hhmm.

How they are displayed is of no relevence, I am happy with hhmm or hh:mm, however I need excel to recognise them as 24 hour times.

Is there a simple way to convert them all, or would it be easier to use a formula when adding/subtracting the times from each other to convert the time before calculating?

I hope that makes sense.

I am a bus driver, as such I work shifts and these shifts are individually unique. Unlike a factory where multiple people will have the same shift, no bus driver has the same shift as that would mean we would both be driving the same bus at the same time.

Monday to Friday, there are just under 500 individual duties, with about 300 different duties on a Saturday and a further 100 on a Sunday. So we are looking at a total of about 900 duties.

Each duty has its on detailed duty card, but there is also a duty summary that condenses the information down to start, break and finish times and locations (of which there can be between 1 and 5 of each). This is part of what I am working with.

Second to the duty summaries are the rotas themselves. There are numerous rotas depending on what routes we are allocated, but the all follow the same format. They vary in length from rotating every 4 weeks to every 70 weeks. Each line on the rota has 7 entries (1 for each day of the week), and the entries either show a duty number or a rest day.

It is then up to us to cross-reference the rota to the duty cards to fill out our diaries to make sure we turn up on time.

What I am trying to do is 3 things:

1. Combine the summary with the rota so there is no more need to manually cross reference. And to be fair, this I have managed.

2. Create an export file to update Google Calendar with multiple rota lines at a time, to save manually inputting everything. - Again, this I have managed.

3. To calculate the driving hours so we can work out how much time we have for overtime - This I have yet to accomplish due to converting the time from 4 digits base 10 to hh:mm (with or without the ":").

I am currently copying the data from a PDF, pasting into a text file, concatenating each duty into 1 line so that the duty number matches up with the line number in the spreadsheet to make the formulas easier, and then importing that text file into Excel.

I have tried importing from text as space deliminated or copying the text in and using text to columns with space delimination.

The problem is I cannot get Excel to see the times as times. I really don't care whether or not the colon is visible as a separator between the hours and minutes, I just need Excel to recognise the hours and minutes.

But when I select "Format Cells" > "Time" it alters the result.

For example, 1045 should be read as 10:45. However, when I convert the cell from "general" to "time", 1045 gets converted to 10/11/1902 00:00:00.

If I try using custom formatting the following results:

• hh:mm = 00:00

• [hh]:mm = 25080:00

And this is why I asked the question. Simply formatting the cells doesn't render them calculable and with well over 3000 times, there are too many adjust manually.

I will settle for using a formula to convert a 4 digit base 10 number into hh:mm when calculating the driving hours, but I would rather Excel just recognise the data for times.

I hope this makes more sense and brings this back on topic.

Answer 1

1. Select the cell(s) where you want to display 00:00 times instead of 4 digit numbers (it does not matter if some cells are empty and/or if some cells have 4 digit numbers in already).
2. Right-click on the cells and select Format Cells... from the right-click menu
3. On the box that appears click Custom
4. Delete the word 'general' (highlighted in black)
5. Type 00":"00 where you just deleted 'general' and press ok
6. Now, when you type a 4 digit number e.g. 1856 into one of the cells you have formatted, it will be converted into a time e.g. 18:56 (It also keeps 0s at the start of numbers, e.g. 0203 becomes 02:03 and not 2:03). This will also change the format of any numbers currently typed in the formatted cells, and even if excel has already removed the 0 from the front of the number it will be put back in.

Answer 2

I'm not sure I understood how your text file is formatted, but hopefully you can adapt my approach for your situation.

I use File, Open to import my simple text file (with a .txt extension) that has one 4-digit time on each line ("0940"). In response to the prompts, I choose "Text" for the format. This loads all my data into column A as four-digit numeric strings ("0940"). Excel warns me that I have numbers formatted as text, but it's not a problem. In column B I put the following formula, which formats my string to look like a time ("09:40"), and then converts the string to an actual time using the timevalue() function. Here's the formula:

=TIMEVALUE(CONCATENATE(LEFT(A1,2),":",RIGHT(A1,2)))

The resulting columns A and B look like this (you may have more decimal places):
0940 0.40
1200 0.50
2400 0.00
0001 0.00

Once I format Column B as a time, it looks like this:
0940 9:40 AM
1200 12:00 PM
2400 12:00 AM
0001 12:01 AM

You can now use it for calculations. If you want to get rid of the formulas and just keep the time values, you can Copy column B and then Edit, Paste Special, Values.