Calculating & Displaying time differences

**matt002** · 03-11-2009, 06:41 PM

Hello!

Firstly, nice forum here, a credit to the moderating/administration team. I am new to formulas with Excel, despite using the actual component in MS Office for years. It is only now I am requiring something at work that I have had that blank feeling with getting Excel to do what I want it to.

So, I am posting this for someone more knowledgable to hopefully talk me through how to do the following. Ill try be as specific as possible and sorry if I do not get my points across.

Basically, I have a rota at work. It has data for each week for the staff shifts which are all in the day being from 8am - 8pm; usually no later or earlier.

In each cell, I have shifts displayed like "9.00-6.00" which I ideally want to keep the same. However my issue is calculating the time difference in hours/mins (h.mm) without having to change the display as in my example and also calculated based on one cell rather than two.

I want to be able to have excel automatically identify in another cell using formulae that 9-6 is an 9 hour shift without having to adjust AM/PM, use 24hr clock & use seperate cells for each start and finish time.

I can get it to work using a cell for start time and a cell for finish, but then again even then excel doesnt understand me when I use non 24hr or AM/PM time. Ideally I need to add a rule to say that 9-6 is always going to be within the day never at night, or have the formula work in 24hr, but display in the cell as 12hr.

Am I clear in what I describe?

Thanks for any help in advance,

Matt

**shg** · 03-11-2009, 07:24 PM

Just use a two-column lookup table, with shift names (e.g., "9-6", or something totally arbitrary -- "swing". "graveyard") in the left column and length in the right. Then use a VLOOKUP formula to get the hours from the shift name.

**matt002** · 03-11-2009, 07:28 PM

Hello and thanks for the reply.

You have lost me with that response sorry. I am an idiot when it comes to formula in excel or any advanced functions. The most I can do is use addition, subtraction, divisions and multiples in cells, so although its a ball ache, I would appreciate highly a more detailled description into how to perform what you mention.

Thanks for your time once more.

Matt

**daddylonglegs** · 03-11-2009, 07:34 PM

Hello Matt,

If you have a limited number of different shifts I'd go with shg's suggestion. If you want to have the option of flexible shifts, e.g. you might want 9:10-6:15 or 9:15-6:10 then read on.......

It would be easier if you used colons to separate hours and minutes as that's closer to a recognised time format, e.g. 9:00-6:00, is that possible?

Are all start times before noon and finish times after? If so, and assuming use of colons you could use a formula like this to get the total hours

=(MID(A1,FIND("-",A1)+1,5)-LEFT(A1,FIND("-",A1)-1)+0.5)*24

**shg** · 03-11-2009, 07:38 PM

If DLL's suggestion works for you, then by all means use it.

If you want to try what I suggested, then post a workbook with a useful number of examples.

**teylyn** · 03-11-2009, 07:40 PM

Here's a solution based on the following assumptions:

- the shift description will always have a dash separating the times
- the times will always be full hours, i.e. no 9.15-6.30
- the times are in column A

Please Login or Register  to view this content.

**matt002** · 03-12-2009, 05:35 AM

Hello everyone,

I have had some success with using DDLs formula and suggestion, as it seemed the most relevant to my spreadsheet.

I can use a semi colon no problem, obviously I would prefer . as its easier using the numpad, however the only thing I cannot change is that some shifts start after 12 noon. Eg. 5:00-8.15pm. Basically, shifts can run from as early as 8am to a latest of 8pm.

So is there any method to combat that, and furthermore for the cells calculating the shift time differences, is it possible to get that to display in time format rather than just decimals?

I appeciate everyones assistance.

Matt