Time formula

[Dottie]

Working with a spreadsheet (Excel 2007) that has a start time, end time and
time
Start Stop time
14:00 15:18 1:18
Would like to convert time to minutes and add 15 minutes to the answer. I
have set up a custom format for minutes [m] but can't get the +15. Any help
you can give me will be appreaciated.

 

[RagDyeR]

Try this:

=MOD(B1-A1,1)+15/(60*24)
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Working with a spreadsheet (Excel 2007) that has a start time, end time and
time
Start Stop time
14:00 15:18 1:18
Would like to convert time to minutes and add 15 minutes to the answer. I
have set up a custom format for minutes [m] but can't get the +15. Any help
you can give me will be appreaciated.

 

[Mike H]

Hi,

Try this

=(B2-A2)+TIME(0,15,0)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[dgnamu]

hi,

can someone helps me to solve this problem?

name no books read month
nasir 1 12-jan-2010
adam 2 12-jan-2010
fatima 3 14-jan-2010
nasir 2 2-feb-2010
adam 1 2-feb-2010
fatima 1 3-feb-2010
nasir 1 7-mar-2010
adam 4 16-mar-2010
fatima 1 21-mar-2010
nasir 1 17-apr-2010
adam 4 18-apr-2010
fatima 1 24-apr-2010

now, what i'm doing is i'm just selecting the specific month and do the
counting. The formula is Sum(B2:B4) for january. this come to a problem if
the array is not sort accordingly.

what i want is, i'm trying to get the total no of book read in a specific
month like jan or mar. but the formula should cover from jan-apr. please
someone helps me to generate the formula.

 

[Luke M]

=SUMPRODUCT(--(MONTH(C2:C100)>=1),--(MONTH(C2:C100<=2),B2:B100)

Where 1 represents Jan, and 2 represents February. If you want only 1 months
data, change btoh of these numbers to the same value.

 

[Dottie]

Mike, thank you it works great. Now they want to convert the answer to
minutes. I tried add [m] to formatting but get a really weird answer.

Again, thank you in advance for your help.

Hi,

Try this

=(B2-A2)+TIME(0,15,0)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Working with a spreadsheet (Excel 2007) that has a start time, end time and
time
Start Stop time
14:00 15:18 1:18
Would like to convert time to minutes and add 15 minutes to the answer. I
have set up a custom format for minutes [m] but can't get the +15. Any help
you can give me will be appreaciated.

 

[Luke M]

If you really just want a single digit answer:
=(B2-H2)*24*60+15

Will convert the normal decimal value of time into a integer representing
minutes.

--
Best Regards,

Luke M

Mike, thank you it works great. Now they want to convert the answer to
minutes. I tried add [m] to formatting but get a really weird answer.

Again, thank you in advance for your help.

Hi,

Try this

=(B2-A2)+TIME(0,15,0)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Working with a spreadsheet (Excel 2007) that has a start time, end time
and
time
Start Stop time
14:00 15:18 1:18
Would like to convert time to minutes and add 15 minutes to the answer.
I
have set up a custom format for minutes [m] but can't get the +15. Any
help
you can give me will be appreaciated.