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.

0
Utf
5/18/2010 3:41:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
772 Views

Similar Articles

[PageSpeed] 44

Try this:

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

HTH,

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


"Dottie" <Dottie@discussions.microsoft.com> wrote in message 
news:38EC0DA2-5AF0-4BEB-80B6-8C9AB5F5B0B3@microsoft.com...
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.


0
RagDyeR
5/18/2010 3:53:21 PM
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.
> 
0
Utf
5/18/2010 3:57:00 PM
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.


0
Utf
5/18/2010 4:21:01 PM
=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" <dgnamu@discussions.microsoft.com> wrote in message 
news:1F6AF632-4619-4C5A-B0AD-F4992C6CE394@microsoft.com...
>
> 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.
>
> 


0
Luke
5/18/2010 5:02:59 PM
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.
> > 
0
Utf
5/18/2010 6:06:10 PM
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" <Dottie@discussions.microsoft.com> wrote in message 
news:E78BBDBB-5034-4F1C-959F-4C2CEFE22B8C@microsoft.com...
> 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.
>> > 


0
Luke
5/18/2010 6:12:24 PM
Reply:

Similar Artilces:

How to refer to current sheet in a formula
I want to have a named formula which always refers to a cell on the sheet it is on. When I define the formula it is automatically changed to refer to the sheet active when I defined it. E.g. =$B$2*3 becomes =sheet1!$B$2*3. This is no good for use on sheet 2 where I want the formula to refer to that sheet's $B$2 (equivalent of sheet2!$B$2). I have tried entering =!$B$2*3 which works initially but recalculates using the $B$2 on the sheet active when the recalculation is done. -- Poxypig ------------------------------------------------------------------------ Poxypig's Profile: http:/...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

Automatically fill down formulas when adding a record
I have a data entry sheet which receives one record per row. Some of th columns contain calculated fields and will be hidden. Is there a way t have the formulas automatically fill down when new records are added Thank you -- Message posted from http://www.ExcelForum.com One way is to use Excels built-in dataform. data>form -- John johnf 202 at hotmail dot com "steveski >" <<steveski.11dx8c@excelforum-nospam.com> wrote in message news:steveski.11dx8c@excelforum-nospam.com... | I have a data entry sheet which receives one record per row. Some of the | columns con...

date formulas
Hi, I have two problems that need help. Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a dob of birth, cell A2 returns 110. Another one is having cell C1 with a date or NA. C2 is based on cell C1 and adds 3 months to the date in C1 but if C1 has NA, the return is #value!. Kathleen Describing the problem is only the first step, Kathleen. You also need to identify the solution you are looking for. As a guess, for #1 try, =if(a1="","",datedif(a1,today(),"y")) For #2, =if(c1="NA","",date(y...

sum if formula
=SUM(IF('Day 1'!$C$4:$C$37=A22,'Day 1'!$D$4:$M$37))+SUM(IF('Day 2'!$C$4:$C$37=A22,'Day 2'!$D$4:$M$37))+SUM(IF('Day 3'!$C$4:$C$37=A22,'Day 3'!$D$4:$M$37))+SUM(IF('Day 4'!$C$4:$C$37=A22,'Day 4'!$D$4:$M$37))+SUM(IF('Day 5'!$C$4:$C$37=A22,'Day 5'!$D$4:$M$37))+SUM(IF('Day 6'!$C$4:$C$37=A22,'Day 6'!$D$4:$M$37))+SUM(IF('Day 7'!$C$4:$C$37=A22,'Day 7'!$D$4:$M$37))+SUM(IF('Day 8'!$C$4:$C$37=A22,'Day 8'!$D$4:$M$37))+SUM(IF('Day 9'!$C$4:$C$37=A22,'Day ...

Formula Bar missing
I need help reappearing my formula bar. I must have pressed something that made it disappear. If I go to the “View”menu, Formula Bar has a check next to it, as if it should be showing, but I can’t see it. I tried checking and un-checking, but no where to be seen. I have two other tool bars and my status bar, that are working fine. Could anyone give me a hand with this? Thanks, I am currently using Office v. X. You may have switched "Full screen" on. To uncheck, see the View menu -- Kind Regards, Niek Otten Microsoft MVP - Excel "JorgeH" <JorgeH@d...

View one record at a time in a report.
I am new to access. I created a report from a table using the wizard. When I view the report, I only want to see one given record at a time - not all records. How do I do this? Thank you Ron The easiest way is to start from a form, where you bring up the record you want to see. Then put a command button on the form, to open the report to just that one record. Here's the code you need for the command button: Print the record in the form at: http://allenbrowne.com/casu-15.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne...

Add to Formula
I have created a drop down list and added a formula to show me the cost of an item eg; pink slippers when that is picked from the list the price appears in the next column..that is all working fine.....=IF(C3="","",VLOOKUP(C3,'Sheet3 (2)'!A3:C152,2,FALSE)) I've been trying to add another formula to this for inventory. So actually what my question is. How do I create a list along with a formula so when I pick the item out it will put in the price, and minus 1 or however many sold, to give me a count of what I have left in stock, and say I started with 25 of...

Case Billable Time and Total Time in workflow
Hello evereyone, Case Billable Time and Total Time fields don't accessible in workflow! How can I calculate Case duration in workflow or access aforesaid fields? -- Mohsen Ahmadi, msnahm@hotmail.com IR-0912 *** **** ...

formatting cells to show time format
Hi I wonder if you can help me with the following. I use Office 97 Professional, and Excel 97 to do the following. I have created a spreadsheet that will be used to calculate times & results for Car Rallies. I have sorted out the formatting of the cells so they show hours, minutes, seconds, and down to thousands of a second, using this time format hh:mm:ss.000. I have done the formulas to work out the elapsed times and then total times, by taking the finish time from the start time etc, then adding elapsed times together to get total times. The thing I am trying to sort out is wh...

= Text formula problem
Hi Everyone, I am using an excel spreadsheet as a linked table in an access database for an update query. I had it working very nicely for a period of time. I am not sure if some Microsoft update caused a problem or what. The field I use t o update my database didn't work until I used this formula to make a new row. I have a column of numbers which was retrieved from a data miner program it's called Acct. For some reason Access doesn't like the formatting so I insert a blank column to the right and call that PtNum. In the first cell of this column which is C2, I type ...

Report Taking a Long Time to Print
I have a main report with 2 subreports in it. One of the subreports seems to be holding up the report when it comes to running and printing. When I run the query and/or report for the problem subreport it returns data within 1 minute. When I run the main report, it takes anywhere up to 5 minutes to run and about 12 minutes to print. When this subreport is removed from the main report, running and printing is under 1 minute, so I know this subreport is the problem. I had seen a post similar to this question and I tried the suggestions (removing page breaks, turning force new page ...

SSN printed with extra dashes 1 time
I have a database storing soc sec numbers and names. The soc sec numbers are entered on a form with a picture clause of 000\-00\-0000;0;_ I have a report (complicated format) that grabs information from the form and prints out the Soc Sec Number. It has a picture clause of @@@-@@-@@@@ For one client and one client only, the SSN prints out 111-2-3--5555 All others print out 111-23-5555 Does anyone know why this happens. Win XP SP2 and Access 2003 system running Thanks in advance It is very probable that the SSN that has the problem already has the dashes in it or is ot...

Why Does This Formula Return an Error??
:confused: Hi, what am I doing wrong with the following formula: =countif(sheet1:sheet31!E6:E35,">=1") FOR some reason this formula fails when I use the (sheet1:sheet31) wit the column range (E6:E35). Is it possible to include a multiple sheet count and column range i the same Formula? Thanks for any helpful responses.. By the way, the formula works ok just as long as I'm not trying to rea multiple sheets.. Please help on this -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=...

receiveing the same email three times #2
My outlook express died on me, therefore I had to start using outlook 2000, which was fine bit of a pain to get used to but, anyway since using outlook 2000, I am getting the same email from everyone three times. Why is this? It is soing my head in as my outlook is very full and it is taking me some time to get rid of the ones that are duplicated. PLEASE HELP ANYONE!!!!!!!!!!!!!!!!!!!!! ...

Time Difference
Dear All, I have some doubt in Date & Time Format. I have some (time) appearing in a column in this format 7/17/2006 3:20:53 PM.. I need to have a conditional formatting for this in that column. Ie., if the time in the column is less than 20 minutes to the current time (ie.,now()) then it should change to some color. Any help in this regard. Rajkumar -- Rajkumar Eager to learn more about Excel ------------------------------------------------------------------------ Rajkumar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2159 View this thread: http:/...

Formula not deleted when pressing delete???
Hi, Is it possible to protect Formula's in a cell from being deleted whe user pressing delete? Only the cells who containt formula's may not be cleared but th others may! Thanks in @dvance for trying to help me. GreetZ from Stif -- Message posted from http://www.ExcelForum.com Stift, Select the cells that are allowed to be changed. Format - Cells - Protection - Unlock. Now protect the sheet: Tools - Protection - Protect Sheet. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Stift >" <<Stift.18ct...

Drag Formula cell to decrement the cells #2
Ken, I do thank you for your prompt assistance. The solution you gave me works fine but with limited No. of cell (sorting problem) is there a way to do it in an unlimited No. of cells (e.g. 50 or 6 cells - BI till DP to be the mirror of A till BH ) Thanks Agai -- Asher Zu ----------------------------------------------------------------------- Asher Zur's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1610 View this thread: http://www.excelforum.com/showthread.php?threadid=27554 Sure, just follow instructions as before and in BI1 put =A1 and copy across to DP...

OWA Calendar Time
In my OWA there is a discrepancy between looking diary entry and when actually double-clicking on it. The appointment times are an hour behind in the details of the appointment.elay. This does not happen on the Outlook client. The time zone, daylight saving settings are correct on the Exchange server, Outlook client, OWA Options settings, client PC as well. Any suggestions will greatly appreciated. Regards Issue resolved. For anyone inetrested in knowing the solution.... Went into the Time Zone settings on the Exchange server and set it to Hobart time zone (we are in Sydney by the way)...

Setting Retention times for Mailboxes
Using Exchange 2000 SP3. Have a user that claims some email are missing from Outlook inbox from over a year ago. Is there a default setting within Exchange in which mailbox items get deleted after a certain period of time? (I am not talking about deleted item retention periods) Thanks On 18 Jan 2006 06:27:27 -0800, melickas@yahoo.com wrote: >Using Exchange 2000 SP3. Have a user that claims some email are missing >from Outlook inbox from over a year ago. Is there a default setting >within Exchange in which mailbox items get deleted after a certain >period of time? (I am not talk...

Protect: Formulas?
I'm protecting a sheet via VBA. There seem tb quite a few parms to control what gets allowed/disallowed, but I can't find anything that applies to formulas. I want the user to be able to put formulas behind some cells. Right now, with the sheet protected, the user can type data into cells, but the Sigma icon that allows formulas tb put behind them is disabled. Unprotecting the sheet enables the icon... but I want various things to remain protected. Is there a way to allow formulas, but still have Protected=True? -- PeteCresswell Pete, You have to have unlocked the cells the use...

Need Help Nesting a Formula inside MAX formula
I have Stock Market quotes that gets stored in Excel intra-day When every 30 minutes passes, the data is automatically updated and archived in the next row up and the new update begins. EXAMPLE: C2 9:30 D2 25 C3 10:00 D3 25.5 C4 10:30 D4 25.10 C5 11:00 D5 26 After 30 mins the data looks like this: C2 10:00 D2 25.5 C3 10:30 D3 25.10 C4 11:00 D4 26 C5 11:30 D5 26.25 This process keeps going on. I can archive as many periods I want, in any timeframe. I currently store 100 rows of 30 minute periods. Everytime a new peiod begins al...

Strange Problem with Formulas
Dear users, I was confronted with a very strange problem. Can anybody help? Basically, I have 3 worksheets: No. 1 contains all data No. 2 contains all filtered data (macro special filter from No. 1) No. 3 is the output, graphically optimized sheet, which has fixed formulas to No. 2 (e.g. =Event1!A1) Imagine, I had only these 3 sheets. Actually, I have many of them, but there are all paired as No. 1 and 2. No. 3 is the main sheets, which links too all No. 2's. To update the file, I have created a macro, which compares one excel file with the actual one. All worksheets named similarily ...

Two formulas in one cell?
Hi all, Is it possible to have two formulas in one cell? For example, here's what I'm trying to do: If cell A1 has the word HORSE in it, I want the formula in A2 to be x + z If cell A1 has the word PIG in it, I want the formula in A2 to be x * z Anyone know if that's possible? Many thanks, Matt. If the lower upper case does not matter =IF(A1="horse",x+z,IF(A1="pig",x*z,"")) if it matters =IF(--EXACT(A1,"HORSE")=1,x+z,IF(--EXACT(A1,"PIG")=1,x*z,"")) or =IF(ISNUMBER(FIND("HORSE",A1)),x+z,IF(ISNUMBER(F...

Not having to wait for routine to finsh / run three events at the same time
I developed a simple app, it goes out to an access db and populates a list box and pings as server. Pretty much how can I do both events at the same time without haveing to wait for one to complete before moving on to the next one. It acts like an application monitor where I ping the servers every 15mins and see if they are up, they are totally seperate Public routines, sound simpel but it is not. I just want to ping all three severs and get the ping times. I have everything coded it is just the waiting that has me stuck! Thanks Your problem is a typical example where a backgroundwork...