Editing the first two digits of a cell

[gary_savage]

I would appreciate any help....

I have lots of spreadsheets with times on them, I'm trying to get the average runtimes of each line but I am having problems calculating it as some of the end times end after midnight so it thinks it's on the same day as when the job started E.G.

Jobname Start Time End Time

Example 22:45 01:05

I have worked out a way to correct this problem by changing the 01 to 25 00 will be 24 and 02 will be 26 etc. All I need is a macro that will change this for me or I have to go through five months worth and edit it manually.

Thanks in advance

Gary

 

[Aladin Akyurek]

I would appreciate any help....

I have lots of spreadsheets with times on them, I'm trying to get the average runtimes of each line but I am having problems calculating it as some of the end times end after midnight so it thinks it's on the same day as when the job started E.G.

Jobname Start Time End Time

Example 22:45 01:05

I have worked out a way to correct this problem by changing the 01 to 25 00 will be 24 and 02 will be 26 etc. All I need is a macro that will change this for me or I have to go through five months worth and edit it manually.

Thanks in advance

Gary

Try...

=B1 - A1 + (B1 < A1)

 

[gary_savage]

Thanks for your reply but it's not that bit I need to change, I need to change the times before they are all averaged out your formula will work once I have changed all the times to be correct.

At the moment because it thinks some jobs finished at e.g. 01:00 it is taken the time as 01:00 on the same day as the 22:50 when it started by changing the time to 25:00 which still displays as 01:00 it takes it that it's on the next day