Working with custom formats in Excel

I have an answer to Neil Tarry’s query about sorting data when the working day starts at 1400 and finishes at 0600 the next day. If he formats the relevant cells using the custom format to show hh:mm, then inputs the data starting at 14:00hrs ending with 30:00hrs it will show in the sheet as 14:00 - 06:00 in the correct manner.

Select the cells and press Ctrl and 1 to show the Format Cells window. Click on the Number tab and then on Custom in the Category window. Scroll down the list in the Type list and left-click once on hh:mm. Click on OK. If 26:00 is entered for two o’clock in the morning it will show on the spreadsheet as 02:00 in the correct place. Anything beyond 06:00 in the event of overtime etc will be input as 32:00 = 08:00 and so on.

Tom Gibbard

Baffled by jargon? See our free online Jargon Buster.