Time formula

  • Thread starter Thread starter Dottie
  • Start date Start date
D

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.
 
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.
 
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.
 
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.
 
=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.
 
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.

Mike H said:
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.


Dottie said:
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.
Click to expand...
Click to expand...
 
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
Dottie said:
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.

Mike H said:
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.


Dottie said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top