Calculating total time based on start and end time.

[famin]

Hi,

I am currently working with some data that include columns with start and end time of bus trips. The time-stamps don't come in a time cell format but rather in a general one.

The problem is I cannot simply subtract those timestamps as sometimes the end date is on the next day and there is no full date data included, it is just the timestamps.

I tried several things, with mixed results. For example I made an if function where in case "B (end time) < A (start time)" then "24+B-A" if not "B-A". The problem with this approach is that it treats the time-stamps as numbers and whereas for "o'clock" ones is totally fine, that is not the case where minutes are involved. Changing the cell format to date, solves the minutes problem but adds another since time is a 24 system which renders the formula above as simply useless.

Any input would be appreciated.

[JohnTopley]

Please post a sample file with clear explanation of what is required and expected results.

To upload file, click "Go Advanced" then "Manage Attachments"

[famin]

Well here it is, the columns in question are "I" and "J". What I need is to estimate the total travel time based on those timestamps.

Thank you

[José Augusto]

Hi
Use these formula and forma the result as hh:mm (for row 5)

Please Login or Register  to view this content.

or use
=TEXT(-SUBSTITUTE(I5,".",":")+SUBSTITUTE(J5,".",":"),"hh:mm")

[famin]

Hi Jose,

I am getting a formula error when inputting this formula in column "T".

Thanks

[José Augusto]

Hi
See the file START_END.xlsx

[famin]

Oh I see.

Well that made format conversion and subtracting in one go.

However there still is the original issue when the start time is on day A while the end time is on day B.

You can see what I am talking about if scroll a little to those cells with a value error near the bottom of the document.

[José Augusto]

Hi
Use

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[famin]

Jose thanks again for the prompt reply.

Perhaps there is something wrong with my Excel version (2007) because it again gives me an error when pasting the formula.

Any ideas?

[José Augusto]

I do not know what's going on.
See the file START_END.xlsx

[JohnTopley]

I tested both Jose's formulae in START_END.xlxs and they worked, even saving the file as "97-2003" version, so it's hard to see why you are having problems.

[famin]

Yes, I am not suggesting they do not work in general. I am just reporting that there might be some issue with my installation.

I am attaching a couple of screenshots to show you what happens when I am pasting both variations of the formula.

formula_error_1.JPG
formula_error_2.JPG

The error message is in Greek, but what it essentially is saying is that there is some error with the formula.

[José Augusto]

Hi
Clarify me. When you download my file the formulas do not work?
The screenshots you show are from a different file.

[JohnTopley]

Why are first two rows of Column T "hh:mm" rather than numeric results? Is formula copied to wrong cell?

[famin]

Yes the updated file you uploaded works just fine. And not only that but when I am copying the formula in another cell on the same file there is no error message.

START_END_2.JPG

However when I paste the formula in the original file that is another story.

[famin]

Update

When I am copying the formula from the file you provided with right click --> COPY and then paste it to the original file, then again it works fine.



Update 2

And then if for example I press ENTER while editing the formula, it stops to work and "hh:mm" takes its place as you can see here.

[famin]

Why are first two rows of Column T "hh:mm" rather than numeric results? Is formula copied to wrong cell?

Sorry missed that one, no actually it is not. As mentioned just by placing the cursor on the already typed formula and then pressing ENTER this is what happens.