calculating time differences problem

[DUMBASS]

Hi
I know I've asked this before and got a solution but need to resolve the issue again so please bear with me.

I'm running a 24 hour delivery schedule where i need to work out the time difference between start & finish times.
I'm OK until I get to cross midnight range:

A1 Start 19:00
B1 Finish 18:45
C1 +/- 00:15
(happy days and will return negative value if later)

A1 Start 00:15
B1 Finish 23:45 (earlier)
C1 +/- -23:30
(headache)

I know I can modify the formula in C1 for example 2 but schedule changes daily and column ranges from A1 to A300+

so... is there 1 formula I can use for the full column range to return the right result as this is a multi user form and I'm not always there to do the modification.

 

[husoi]

You're looking for formula =TIME(H,M,S) don't forget to format the cells to fit what you need i.e. hh:mm.

 

[joeu2004]

A1 Start 19:00
B1 Finish 18:45

As long as times are within 24 hours, you can use the following in C1:

=B1-A1+(B1<=A1)

formatted as Custom [h]:mm.

Some people suggest MOD(B1-A1,1). Although that is simpler, it returns zero if B1=A1, whereas the formula above interprets that conditions as exactly 24 hours. The latter seems more reasonable, IMHO.

The "[h]" specification formats hours greater than 23. So when B1=A1, the results appears as 24:00 instead of 0:00.

A1 Start 00:15
B1 Finish 23:45 (earlier)

I do not understand the problem. My assumption is that 00:15 and 23:45 are in the same day; midnight is not between them.

If 00:15 is one day and 23:45 is another day (more than 24 hours apart), you must include the dates in the calculation.

In that case, perhaps all times should be recorded as date as well as time. You can use Custom format hh:mm to display only the time, although the displayed times might be ambiguous and confusing. Nevertheless, if you do that, the following formula should suffice:

=B1-A1

formatted as Custom [h]:mm .

 

[DUMBASS]

Thanks for taking the to reply ... but

@husoi - sorry but that means nothing to me (note my user name!!)

@joeu2004
the schedule starts ie Monday(10:00) and runs overnight into Tuesday (06:00)

DUE ACTUA TIME
TIME TIME +/-
19:00 18:45 00:15 (A3-B3)
22:00 21:45 00:15 (A4-B4)
23:30 23:00 00:30 (A5-B5) etc etc
00:15 23:59 -23:44 FORMULA TO RETURN 00:30 (and would ideally apply to all column returns as per 1st post if that makes sense)

 

[joeu2004]

the schedule starts ie Monday(10:00) and runs overnight into Tuesday (06:00)

So times are within 24 hours. The formula should work for you, to wit:

=B1-A1+(B1<=A1)

formatted as Custom [h]:mm.

19:00 18:45 00:15 (A3-B3)
22:00 21:45 00:15 (A4-B4)
23:30 23:00 00:30 (A5-B5)

You should be calculating finishTime minus startTime. Previously, you said that column B is the finishTime and column A is the startTime. So the calculations should be B3-A3, not A3-B3, for example.

 

[husoi]

Thanks for taking the to reply ... but

@husoi - sorry but that means nothing to me (note my user name!!)

@joeu2004
the schedule starts ie Monday(10:00) and runs overnight into Tuesday (06:00)

DUE ACTUA TIME
TIME TIME +/-
19:00 18:45 00:15 (A3-B3)
22:00 21:45 00:15 (A4-B4)
23:30 23:00 00:30 (A5-B5) etc etc
00:15 23:59 -23:44 FORMULA TO RETURN 00:30 (and would ideally apply to all column returns as per 1st post if that makes sense)

Hi DA,

because you want to count in the days where 23:45 will be day 1 and 00:15 is day 2 you have to include that in the information.
the input then will be 1/12/16 19:00 (for your first time)

Due date (A1)	Actual date (B1)	formula (C1)
29/11/2016 19:00:00	29/11/2016 18:45:00	=A1-B1

<tbody>
</tbody>

Use the format suggested by Joeu2004 as custom [h]:mm in the formula.
If you don't want to show the dates format the A1:B1 as hh:mm and it will hide the dates.