time math
 

Is there a way that I can do math on times?

For example, I want to enter the starting time of a work shift in one cell
(using UTC or military time or 24 hour time - call it what you like) and
similarly, enter the work shift ending time (in the same format) and then use
these two values to calculate the number of hours and minutes in the work
shift.

THREE problems arise -

(1) if the commencement time of the workshift is BEFORE midnight and the
ending of the work shift is after midnight, how does one get the correct
answer (e.g. start = 1930 or 7:30 pm and finish is 0600 or 6:00 am)

(2) if the commencement time is after midnight but before the time entry
consists of a number greater than 0959 or 9:59 am - the entry doesn't accept
leading zeros

(3) how can the result - assuming that it is possible to get one from the
above conditions - be displayed in a time format (e.g say the total was 10
hours and 27 minutes I would like the displayed value to be 10:27)

Start End
1930 0930
1930 1230
0000 0600
0000 1230
0255 1510

ANY suggestions would be valued! I'm at wits end trying to figure this one
out! Am I battling a hopeless task?

Thanks!

Hi!

Just enter the times in a 24 hr clock format:

0:00 = midnight
1:00 = 1:00 AM
12:00 = noon
13:00 = 1:00 PM
23:00 = 11:00 PM

A1 = 19:30
B1 = 9:30

Format the cell as [h]:mm

=B1-A1+(B1<A1)

Returns: 14:00

Biff

"USAOz" wrote in message
...
Is there a way that I can do math on times?

For example, I want to enter the starting time of a work shift in one cell
(using UTC or military time or 24 hour time - call it what you like) and
similarly, enter the work shift ending time (in the same format) and then
use
these two values to calculate the number of hours and minutes in the work
shift.

THREE problems arise -

(1) if the commencement time of the workshift is BEFORE midnight and the
ending of the work shift is after midnight, how does one get the correct
answer (e.g. start = 1930 or 7:30 pm and finish is 0600 or 6:00 am)

(2) if the commencement time is after midnight but before the time entry
consists of a number greater than 0959 or 9:59 am - the entry doesn't
accept
leading zeros

(3) how can the result - assuming that it is possible to get one from the
above conditions - be displayed in a time format (e.g say the total was 10
hours and 27 minutes I would like the displayed value to be 10:27)

Start End
1930 0930
1930 1230
0000 0600
0000 1230
0255 1510

ANY suggestions would be valued! I'm at wits end trying to figure this
one
out! Am I battling a hopeless task?

Thanks!

Hi

A2 - StartTime in time format (entered like 9:59).
B2 - EndTime in time format.
Working time (in time format) calculation:
=B2-A2+(B2<A2)
Full working hours in numeric format:
=INT((B2-A2+(B2A2)*24)
Remaining working minutes in numeric format:
=MOD((B2-A2+(B2A2)*24,1)
or
=ROUND(MOD((B2-A2+(B2A2)*24,1),0)

To accept only full hours after 9:00 until midnight for starting time:
=B2-IF(A2<9/24,A2,INT(A2*24)/24))+(B2<A2)

Format the result of working time calculation as custom "hh:mm"


Arvi Laanemets



"USAOz" wrote in message
...
Is there a way that I can do math on times?

For example, I want to enter the starting time of a work shift in one cell
(using UTC or military time or 24 hour time - call it what you like) and
similarly, enter the work shift ending time (in the same format) and then
use
these two values to calculate the number of hours and minutes in the work
shift.

THREE problems arise -

(1) if the commencement time of the workshift is BEFORE midnight and the
ending of the work shift is after midnight, how does one get the correct
answer (e.g. start = 1930 or 7:30 pm and finish is 0600 or 6:00 am)

(2) if the commencement time is after midnight but before the time entry
consists of a number greater than 0959 or 9:59 am - the entry doesn't
accept
leading zeros

(3) how can the result - assuming that it is possible to get one from the
above conditions - be displayed in a time format (e.g say the total was 10
hours and 27 minutes I would like the displayed value to be 10:27)

Start End
1930 0930
1930 1230
0000 0600
0000 1230
0255 1510

ANY suggestions would be valued! I'm at wits end trying to figure this
one
out! Am I battling a hopeless task?

Thanks!