If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time formula
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. |
#2
|
|||
|
|||
Time formula
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" wrote: 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. |
#3
|
|||
|
|||
Questions
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. |
#4
|
|||
|
|||
Questions
=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. -- Best Regards, Luke M "dgnamu" wrote in message ... 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. |
#5
|
|||
|
|||
Time formula
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" wrote: 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" wrote: 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. |
#6
|
|||
|
|||
Time formula
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" wrote in message ... 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" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|