Time calculation or formula

[Lise]

Hi - very thick question I'm sure but is driving me mad - I have the
following and want the time to travel in minutes, how do I then calculate the
total cell to show hours & minutes ie: 12 hour shift and 90 minute travel =
13 hours and 30 minutes not 12.90?? Any assistance as always appreciated

Mon
Length of Shift Day 12.00
Length of Shift Night
Overtime
Travel time to & From work 0.90
Total 12.90

 

[T. Valko]

Enter the values as TIME:

12:00
1:30

=SUM(A1:A2) = 13:30

Format the total as [h]:mm

 

[Lise]

Thanks Biff - that's ok when I'm entering the details but is there any other
calculation I can use when others deal only in minutes for some travel time
ie .90 instead of the 1:30 - I was hoping to be able to add a formula or
something to the total only cell that would auto calculate a conversion??

Lise

Enter the values as TIME:

12:00
1:30

=SUM(A1:A2) = 13:30

Format the total as [h]:mm

--
Biff
Microsoft Excel MVP

Hi - very thick question I'm sure but is driving me mad - I have the
following and want the time to travel in minutes, how do I then calculate
the
total cell to show hours & minutes ie: 12 hour shift and 90 minute travel
=
13 hours and 30 minutes not 12.90?? Any assistance as always appreciated

Mon
Length of Shift Day 12.00
Length of Shift Night
Overtime
Travel time to & From work 0.90
Total 12.90

 

[pdberger]

Lise --

You're mixing units -- 90 minutes is not 0.90 hours, it's 1.5 hours. If you
format the cells to hh:mm, and enter the times as 12:00 and 1:30, you'll get
the answer you want.

HTH

 

[Lise]

Thanks all - I'll just have to convert the team to all use the one way

 

[FSt1]

hi
computers keep time as serial numbers that represent days. hours and minutes
are kept as a decimal value of 1 day. if you enter 12, excel will interpet
this as 12 days.
so you do a little math.
12 (representing 12 hours) in a2.
90(representing 90 minutes in a3
in b2, enter =a2/24.
answer = .5 day. format as military time = 12:00
in b3, enter =A3/24/60
answer = .0625 day. format as military time = 01:30
sum B2+B3. format as military time = 13:30 or 13 hours 30 minutes.
or .5625 day.
key to remember when adding hours and minutes is that you are dealing with
decimal values of a day(not whole numbers) which is then formated to time.

yes i know. seems overly complicated. but time is not based on the normal
number system of 10.
24 hr in a day
60 minutes in a hour
60 second in a minute
7 days in a week.
4 weeks in a month(almost)
30 days in a month(sometimes)
365 day in a year(usually)
anytime your numbering system has almost, sometimes and usually in it....
the calculating math starts getting weird.

now i have cleared things up or totals confused everything.
isn't time worderful?

Regards
FSt1

 

[Bob Phillips]

You could create formulae to work on numbers instead of time, but changing
the team working is the right way to go.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)