Time Calculations

  • Thread starter Thread starter Lilbit
  • Start date Start date
L

Lilbit

What formulas would I use for the following:

A1=.30, A2=.30, Total in A3=1 (as in 1 hour)

Adding:
B1=40(minutes)
B2=6:30
Answer in B3=7:10

Subtracting:
C1=40(minutes)
C2=6:30
Answer in C3=5:50

Thanks in advance for your help!!
 
=C2-(C1/1440)

=B2+(B1/1440)

=100*SUM(A1:A2)/1440

or if you want the last in decimal hours

=100*SUM(A1:A2)/1440*24


format the first 2 as hh:mm and the last either hh:mm or general
 
Lilbit said:
What formulas would I use for the following:

A1=.30, A2=.30, Total in A3=1 (as in 1 hour)

Adding:
B1=40(minutes)
B2=6:30
Answer in B3=7:10

Subtracting:
C1=40(minutes)
C2=6:30
Answer in C3=5:50

Thanks in advance for your help!!

Lilbit,
Click to expand...

It'd probably help if you converted your .30's into .5 for a half hour
worked.. that's the first thing i'd do.

HTH
Kevin
 
For A) =(A1+A2)*10/6 but if A1=.30 and A1=0.15 you get 0.75 for 3/4 hr
For B) =B2+B1/(60*24) 40/60 give hours but Excel keeps time values as
fractions of a day hence the 24
For C) =B2+B1/(60*24) ditto
best wishes
 
First, you're gonna have to decide on how you enter time.

I'd enter:
00:30:00
for 30 minutes

Then you can just =sum(A1:A2) or =a1+a2
to get the total
(Format the results as [hh]:mm:ss if you could go over 24 hours)

Same thing with the
00:40:00
06:30:00
to get
07:10:00

By entering .30 for 30 minutes and 40 for 40 minutes is going to cause lots of
problems.
 
Time and dates are numbers with special formatting. Since they are stored as
numbers you can treat them as such. Once you understand that, most of your
problems will go away. Think of time as portions of a day. 6:00 AM is
stored as 0.25. 6 hours is 1/4 of a day. Just remember to input time as
time (ie 30min should be entered as 00:30). If you a time output in decimal
hours (6.5 instead of 6:30) you need to multiply that cell by 24 and format
it as a number.

Example 1:
Input
A1: 0:30
A2: 0:30
A3: =A1+A2
Formatted as (using a custom number format)
A1: \.[m]
A2: \.[m]
A3: [h]
Will look like this
A1: .30
A2: .30
A3: 1

Example 2:
Input:
B1: 00:40:00
B2: 6:30:00
B3: B1+B2
Custom Number Format
B1: [m]
B2: h:mm
B3: h:mm
Will look like
B1: 40
B2: 6:30
B3: 7:10

Example 3:
Same as Example 2 except
C3: =C2-C1
Will look like
C1: 40
C2: 6:30
C3: 5:50
 
Back
Top