Calculating Times

[DB]

In excel 2000 I'm trying calculate a total time in
minutes. I have two cells: one cell is the begin time and
the other is the end time. Time is input as a 24 hr
format. The result is formatted with the code M to keep
only minutes format.

I need the difference bewteen end time & begin time
(result should always be in total minutes - not hrs/min -
example 90 minutes not 1.5 or 1:30 etc.).

If I format the cells as a 24 hr format (with a code of M
to keep in the minutes format) then simply subtract -
that works except when the result > 59. The code M is
only good for 0-59.

Also I need to also properly get the time difference when
crossing over the midnight hour ie begin time 2305 end
time 0047.

Also when using the 24 hour format is there a way in
excel to use an input mask to place the : symbol so time
can be input just using the numeric keypad and not have
to type the : symbol.

Any help would be greatly appreciated.

Thanks
DB

 

[Biff]

Hi DB,

A1 = 23:00 begin time
A2 = 01:00 end time

=(A2-A1+(A1>A2))*1440

returns 120

There's really no need to format as 24 hr clock with code
M. If you simply enter as 23:00, XL automatically
recognizes that as a time entry and the code M is what's
stopping the calculation at 59 mins.

As for eliminating the ":" entry, I'm not so sure about
that one. Without it, 2300 is just another number. 23:00
looks like a time entry in appearance only. The actual
underlying value is 0.958333333.

I don't know how XL can evaluate 2300 and 0.958333333 as
equal.

Biff

 

[DB]

Thanks Biff

The formula:

=(E6-E5+(E5>E6))*1440

looks like it will work great!

But I'm trying to work out a custom format to
automatically put the : in the cell.(E6 or E5)

Example I type 1345

when I hit the enter key the format or input mask would
automatically return:

13:45

In access this is referred to as an input mask, In excel
I don't know! I've trying to do this with a custom format
but no success yet.

Any more assistance would be super

Thanks
DB

 

[J.E. McGimpsey]

You can't do it with formatting. Take a look here:

http://cpearson.com/excel/DateTimeEntry.htm

 

[M. lane]

Hi DB,

A1 = 23:00 begin time
A2 = 01:00 end time

=(A2-A1+(A1>A2))*1440

returns 120

There's really no need to format as 24 hr clock with code
M. If you simply enter as 23:00, XL automatically
recognizes that as a time entry and the code M is what's
stopping the calculation at 59 mins.

As for eliminating the ":" entry, I'm not so sure about
that one. Without it, 2300 is just another number. 23:00
looks like a time entry in appearance only. The actual
underlying value is 0.958333333.


I don't know how XL can evaluate 2300 and 0.958333333 as
equal.

If you run calcualtions on it you will find that 0.958333333 is near to the
percentage of 23 hours out of a 24 hr day.
0.958333333 /23 *24 =999999984