Calculating time on site from a 24 hour clock

[jatacake]

I am exporting from a database into an excel file.
It provides me with On Site and Off Site times, but I require the time spent on site. The 24 hr clock does not export with colons, eg
On Site[INDENT]Off Site[INDENT]Time On Site
2103[INDENT] 2214[INDENT] ?
Is there a formula which can calculate the time spent on site, and then if it exceeds 30 minutes?

[FDibbins]

Hi and welcome to the forum

It would be easier to offer a suggestion if we could see some samples of the actual data you are working with
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jatacake]

I have a attached an example document.

[FDibbins]

Looking at your data, you can just use...

=E2-D2

Make sure you format the cell to number (or general) - it is currently text

[jatacake]

Sorry not that simple, though the 24hr clock presents in a decimal format, an hour still only has 60 minutes, therefore eg on at 0053 off at 0101 is in fact only 8 minutes on site, though the formula suggests 48. I originally played with subcontracting 40 from each result, but that would only apply to when the Time Off Site goes into another hour period as per above

[FDibbins]

ok yes, sorry, I see that. try this instead....

=(TIME(VALUE(LEFT(E2,2)),VALUE(RIGHT(E2,2)),0)-TIME(VALUE(LEFT(D2,2)),VALUE(RIGHT(D2,2)),0))
format as time minutes:seconds....30:55.2

[Kevin UK]

Hi jatacake

Also another way as below and format the cells as "mm:ss" or "hh:mm:ss" if required.
=MOD((REPLACE(E2,4-1,0,":")+0)-(REPLACE(D2,4-1,0,":")+0),1)

[jatacake]

Thanks for the reply.
I have attached the spreadsheet with the formula inserted, the result is not what I need, ie I have inserted what the actual answers are, ie calculated manually. Do you think there is a way to achieve this?

[dredwolf]

Look at the attachment below, I added a column just to check against your results, and custom formatted the cells as ' [m] ', without the (') apostrophes or spaces

the formula used was :

Please Login or Register  to view this content.

it may be overkill, but it insured that they came out as valid times

Hope this helps

[jatacake]

Fantastic, works fine. Thanks for all your time

[dredwolf]

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

[jatacake]

Thanks and have done.
As a side note, if I was only wanting to record results in excess of 30 min, eg as per attached

[dredwolf]

Try this in H2:

Please Login or Register  to view this content.

Drag down

Hope this helps

EDIT-

Added the file

[Teethless mama]

=max(g2*1440-30,)

[dredwolf]

mama always knows best

[jatacake]

jatacake.sol1 (1).xlsDredwolf, I appologise, but I have found an error, I have attached the spreadsheet showing the example.
It seems if the time on site progresses over midnight, an error is produced.
Appreciate any assistance you can offer?

[dredwolf]

okay then...try this in G2:

Please Login or Register  to view this content.

drag down

Hope this helps

EDIT_
if you got some that go beyond 23.59 hrs, then you are going to have to redesign the sheet, the way it is laid out at this time does not allow for that,...

[Kevin UK]

@ jatacake

Perhaps you need to read the replies carefully.

the result is not what I need

Post #6 & #7 gave you the correct answers.
Instead of formatting as mm:ss all you needed to do format as [m] or for decimal time multiplied by 1440.

jatacake.sol1 (1).xlsDredwolf, I appologise, but I have found an error, I have attached the spreadsheet showing the example.
It seems if the time on site progresses over midnight, an error is produced.
Appreciate any assistance you can offer?

The answer for this error was in post #7!
=MOD((REPLACE(E2,4-1,0,":")+0)-(REPLACE(D2,4-1,0,":")+0),1)