Time Calculations / If Statements

I am needing to do a calculation on time results, however, 
when my time difference involves results from times of 
2400 and greater, my result is inaccurate. See below:
F1    G1
ATA   ETA
2400  0015  (formula: f1-g1) answer is: 2385, but should  
be 15.  2400 is midnight, 0015 is 12:15.  I can't figure 
out how to enter formula so I can calculate difference in 
late arrivals when it's midnight.  Would an "if"statement 
fix this, if so, how do you do that?  Or how do I fix this 
problem?  Your help would be  GREATLY APPRECIATED! :)
0
aeschenko (2)
10/6/2003 7:25:55 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
289 Views

Similar Articles

[PageSpeed] 14

Anita,

use

=MOD(end-start,1)

-- 

Regards,

Peo Sjoblom


"Anita Eschenko" <aeschenko@calover.com> wrote in message
news:03ae01c38c3f$a97289b0$a401280a@phx.gbl...
> I am needing to do a calculation on time results, however,
> when my time difference involves results from times of
> 2400 and greater, my result is inaccurate. See below:
> F1    G1
> ATA   ETA
> 2400  0015  (formula: f1-g1) answer is: 2385, but should
> be 15.  2400 is midnight, 0015 is 12:15.  I can't figure
> out how to enter formula so I can calculate difference in
> late arrivals when it's midnight.  Would an "if"statement
> fix this, if so, how do you do that?  Or how do I fix this
> problem?  Your help would be  GREATLY APPRECIATED! :)


0
terre08 (1112)
10/6/2003 7:37:28 PM
I tried =MOD(F1-G1,1), result I get now is 0000. That one 
did not work. 
>-----Original Message-----
>Anita,
>
>use
>
>=MOD(end-start,1)
>
>-- 
>
>Regards,
>
>Peo Sjoblom
>
>
>"Anita Eschenko" <aeschenko@calover.com> wrote in message
>news:03ae01c38c3f$a97289b0$a401280a@phx.gbl...
>> I am needing to do a calculation on time results, 
however,
>> when my time difference involves results from times of
>> 2400 and greater, my result is inaccurate. See below:
>> F1    G1
>> ATA   ETA
>> 2400  0015  (formula: f1-g1) answer is: 2385, but should
>> be 15.  2400 is midnight, 0015 is 12:15.  I can't figure
>> out how to enter formula so I can calculate difference 
in
>> late arrivals when it's midnight.  Would 
an "if"statement
>> fix this, if so, how do you do that?  Or how do I fix 
this
>> problem?  Your help would be  GREATLY APPRECIATED! :)
>
>
>.
>
0
aeschenko (2)
10/6/2003 8:01:51 PM
If your values were really time (24:00:00), just formatted to look like 2400,
then Peo's formula worked fine for me.

But it sounds like your values are just integers.

If that's the case, this formula worked ok for me:

=MOD(TEXT(A1*100,"00\:00\:00")-TEXT(B1*100,"00\:00\:00"),1)

The text() stuff converts each to a time, then uses Peo's mod(subtraction)
formula.


Anita Eschenko wrote:
> 
> I tried =MOD(F1-G1,1), result I get now is 0000. That one
> did not work.
> >-----Original Message-----
> >Anita,
> >
> >use
> >
> >=MOD(end-start,1)
> >
> >--
> >
> >Regards,
> >
> >Peo Sjoblom
> >
> >
> >"Anita Eschenko" <aeschenko@calover.com> wrote in message
> >news:03ae01c38c3f$a97289b0$a401280a@phx.gbl...
> >> I am needing to do a calculation on time results,
> however,
> >> when my time difference involves results from times of
> >> 2400 and greater, my result is inaccurate. See below:
> >> F1    G1
> >> ATA   ETA
> >> 2400  0015  (formula: f1-g1) answer is: 2385, but should
> >> be 15.  2400 is midnight, 0015 is 12:15.  I can't figure
> >> out how to enter formula so I can calculate difference
> in
> >> late arrivals when it's midnight.  Would
> an "if"statement
> >> fix this, if so, how do you do that?  Or how do I fix
> this
> >> problem?  Your help would be  GREATLY APPRECIATED! :)
> >
> >
> >.
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/6/2003 11:43:03 PM
Reply:

Similar Artilces:

Yet, another TIME problem
Okay, I will attempt to explain my situation without just confusin anyone who reads this. I have a time sheet. It consists of the following fields: Time Started/Lunch Begin/Lunch End/Time Ended/Total Time/Over Time In the first four fields the cells are number format (looks lik military time but in reality it isn't) ie: 0630/1130/1230/1600 In the fifth field, the total time is a decimal value. It always read 8.00 or less and carries the remainder,if any, to the over time fiel which is also decimal value. The overtime has to be reported in quarte hour increments ie: .15, .25, .50, .75....

Automatically Update Date/Time in Template
I can insert a date/time in a new message if I am using Word as my editor, but then I cannot save it as a template (why you would want an auto-updating field in a message that cannot be stored as a template is another question). If I switch off Word as the editor so that I can save as a template, I cannot insert a date/time and have it auto-update. Is there a way out of this conundrum, or should I just chalk it up to another peculiarity of MS software? Thanks for any assistance. ...

Calculate MTD and YTD in a query
I need to calculate sales order MTD totals and YTD totals per month in a query. The query will show the following fields: Part#, Order Period (YYYYMM format), MTD Orders, YTD Orders I am getting the information from our transaction-based information system which stores all orders by date. I will need to group the orders by Part# and period, then sum them by month and by year-to- date. I need to see this information for the current month and year as well as for all previous months and years. It's easy enough to run totals per month, but the YTD is another story. Please help! Thanks Per...

Time calculations over 24hrs
Hi I'm using Excel 2003. I'm trying to calculate the total hours (C1) by subtracting end time (B1) from start time (A1) I have formatted the cells for time. I'm currently using B1-A1 which works OK until the end time goes past midnight. e.g start 19:50 end 00:20 should = 4:30 except i just get an endless series of ####### Thanks for your time. Ian If the result is never over 24 hours try: =IF(B1>A1,B1-A1,B1+1-A1) Ian R;717898 Wrote: > Hi > > I'm using Excel 2003. > > I'm trying to calculate the total hours...

Calculated column label in pivot table?
Hello, I have a data table that includes a date field. What I would like is to be able to extract the month from each datapoint and use those months as the column labels. I'd prefer to do it via a calculated item in the pivot table as opposed to adding columns to the data itself, as the data set is dynamic and others beside myself add records to it. I don't want to have to go into the data source and add formulas each time I want to refresh my pivot table. Is there a way to add a calculated field to a pivot table that can then be used as a column label? Any help would...

Update statement with Incrementing
I would like to use an update statement to update a group of records and have one of the fields update incrementally beginning with 1 (just for this group). Below is an example of code that I thought would work and the error that is being generated. Any ideas? UPDATE dbo.vw_NHSReadyToBill SET BatchID = 470, ClaimID = (SELECT ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS GetCount FROM vw_NHSReadyToBill) Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subq...

Format cells for date or time without having to enter the / or :
I am trying to find out how to enter date and enter time without entering the / and the : each time; i.e. 12312007 and have it appear as 12/31/2007 or 831 and have it appear as 8:31. I used to know how to do this but have forgotten...duh! Cannot be done without VBA See Chip Pearson's site for quickentry code. http://www.cpearson.com/excel/DateTimeEntry.htm Gord Dibben MS Excel MVP On Tue, 14 Oct 2008 07:40:02 -0700, Darcyyc <Darcyyc@discussions.microsoft.com> wrote: >I am trying to find out how to enter date and enter time without entering the >/ and the : each time...

how to access cell calculator
There is a way to cause a small calculator to appear when you click on a spreadsheet cell. How do I activate this feature? I can not find it anywhere and can find no info on it. ...

Need to display customer statement online
I need to display customer’s receivable statements on our company website so the customer can pay their balance online. I don't need it to be live - a copy of what is generated by GP8 each month would be great except how? The statements are all generated by transient temporary tables so I cannot recreate them with stored procedures and reports. I looked at printing to pdf but it comes out as one big pdf containing all the statements! What as mess. There's got to be a solution out there. Am I looking at customizing the statement code in dexterity? We are at GP8 and planning...

Convert to time
How would I go about converting 8.00 to 8:00:00 AM in excel. Can this be done? Floyd Forbes Wrote: > How would I go about converting 8.00 to 8:00:00 AM in excel. > Can this be done? Hi Floyd If A1 contains 8.00 format A2 to Time, then input =(A1/24)+A1 oldchippy : -- oldchipp ----------------------------------------------------------------------- oldchippy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1990 View this thread: http://www.excelforum.com/showthread.php?threadid=57380 Divide by 24 and format as time. =a1/24 (8.50 will convert to 8:30 ...

Run-time error '3075'
Syntax error (missing operator) in query expresssion '[AppInvNum] =' I get the above message running the following code. Basically, I am entering an invoice and then opening another form to apply a credit. This first delete code works when the user quits the invoice entry without a credit applied. This is a case where the user changes mind about invoice entry and, if credit is applied, both the invoice and the application of credit need to go away. 'deletes current job record strSQL = "DELETE FROM [tbl 2 Job] " & "WHERE [InvNum] = " &a...

Calculate a Tolerance
Hello all, I would like to calculate values two cells against dimensional tolerance. Example, Cell B2 has a base line value of 2.000. Cell C2 has the actual dimension, lets say it's 2.004. In cell D2 I'd like to display "PASS" or "FAIL" if the value in cell C2 is greater than or less than cell B2 +/- 0.010. So if cell C2 is 1.899 it's fail. if it's 2.011 it's fail. If it's between those two values it pass. How do I calculate that? Thanks Brian "diablo" <nomail@mail.not> wrote in message news:%23Xu8ZkufHHA.2432@TK2MS...

calender calculator?
Is there a way to calculate dates? E.g. today plus 180 days is what day/month/year? Use the new appointment item. Use natural language when inputting the end date, such as 180d. See what is displayed and there you go. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, peter asked: | Is there a way to calculate dates? | E.g. today plus 180 days is what day/month/year? ...

help with calculating overtime in a time sheet
Hi Guys, I'm sure the question has been asked from time to time, but this on has got me a bit stumped, and looking at other peoples questions, jus confuses me even more! so basically I just wanna be able to enter times say from 0600 am till 1953 with a .5 lunch break Basically what I want from this, is a cell for normal hours up to 10, cell for overtime hours after 10 and a total. i've managed to get a total, and overtime hours, but not normal hours! Then for the totals I want a cell that works out normal hours (which should always be 7 anyway) another cell that works out time an...

Why with Excel Xp takes too much time to open and in 2003 fast?
I have 2 computers, in one I have Excel XP (2002) and in the other I have Excel 2003, I've been receiving files wich I need to check with my Excel XP, the problem is that it takes too much time to open, and when I try to open it with Excel 2003 it open normaly (fast). What can I do? Urgent, please I will thank you to send answer : eguevara2000@hotmail.com Thanks ________________________ Tengo 2 computadoras, una con Excel Xp (2002) y la otra con 2003, he recibido archivos pero con Excel XP tarda mucho tiempo en abrir los archivos, mientras que en 2003 lo hace normal (rapido), a ...

Querry regarding statement periods
When balancing my accounts, the default period is always monthly. (In Ver. 14.0) Is there any way to change this to quarterly half yearly? for accounts that don't get much traffic> TIA Peter. ************ No. But all that happens is you have to change the balanced to date in the genie when you go to balance. "LPS-AU" wrote: > When balancing my accounts, the default period is always monthly. (In Ver. > 14.0) Is there any way to change this to quarterly half yearly? for accounts > that don't get much traffic> ...

How do I calculate the z factor automatically
How do I calculate the z factor automatically by using Hall-Yarborogh equation without changing (Y) every time to reach F=0 ? if any body know .please answer me. Thank you very much khalid EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com Please don't multi-post. http://www.cs.tut.fi/~jkorpela/usenet/xpost.html -- David Biddulph <khalid> wrote in message news:2007820134512gawarh@hotmail.com... > How do I calculate the z factor automatically by using Hall-Yarborogh > equation without changing (Y) every time to reach F=0 ? > if any body know ....

Once only calculation
Hi - I'd be very grateful if someone can help me with a bit of macro cod that might achieve this. Really having trouble as my programming skil is diabolical!! I want to perform a "once only" calculation on rows of cells. When reference cell changes from blank to a value it will trigger calculation in another cell. When the reference cell change subsequenlty it will not affect the value in the calculated cell. e.g. Cell - Formula - Value -------------------- Step 1 A1 blank B1 =A1 value blank Step 2 A1 value 10 B1 =A1 value 10 Step3 A1 value 20 B1 =A1 val...

calculation in menu/tool bar using option buttons and pull-down menus
hello: Like to be able to add a series of calculations into the menu bar. The calculations would require 1 input field, 2 sets of radio (or are they called toggle???) buttons, a pull down list and an answer field that was dependent on the radio buttons, pull down and # inputted. I can do all this in an Excel worksheet fairly easily; however, I'd like to put it up in the menu bar/tool bar area so it is easily accessable no mater what workbook I'm working in. How can I do this? Any places I can read about this on the Web? Thanks for any answers/pointers! For example, the basic con...

Recieving the same email multiple times
Everytime I open my email i get the same emails I already recieved everytime! I am not sure what I am doing wrong if I have something checked I shouldn't or what! Please help me! This is Windows Mail in Vista, correct? 1: Tools | Accounts. Make sure you do not have this account listed more than once. 2: Tools | Accounts | Mail | Properties | Advanced - Uncheck: Leave a copy of messages on the server. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Kaitlyn Phillips" <Kaitlyn Phillips@discussions.microsoft.com> wrote in ...

Prorated Percentage Calculation
I need help with calculating a prorated percentage: at 80% of plan pay 5% of salary at 100% of plan pay 10% of salary at 120% of plan pay 15% of salary I have to prorate % of salary to pay based on the % of plan achieved. (ie: 90% of plan = 7.50% of salary) Is there a formula I can use to do this easily? Thanks again! "macmichele" <macmichele@yahoo.com> wrote in message news:1137696750.930192.54330@g44g2000cwa.googlegroups.com... >I need help with calculating a prorated percentage: > > at 80% of plan pay 5% of salary > at 100% of plan pay 10% of salary &g...

Newbie Time Question #2
Great Info. Thank you very much. Any books you could recommend that would help me in understanding creating formulas like this? I would like to get more out of excel this is great stuff. Most of the books I see are a general overview o the application.... Unless I looking at the wrong stuff -- ACangem ----------------------------------------------------------------------- ACangemi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=858 View this thread: http://www.excelforum.com/showthread.php?threadid=26314 There are a few books on Excel formulas listed here...

Calculated Control
I had a calculated control on a report that used this formula: =Round((DSum("[General Sale Epense]","[Expenses]")-DSum("[Out lot]","[Lot Info]"))/DSum("[Price]","[Lot info]"),4) There is a need to change the criteria so the first DSum only totals "[General Sale Expense]" if the field [Expense Type] has a value of "All Consignors" How do I adjust the formula? Thank you -- Dave Hargis, Microsoft Access MVP DSum("[General Sale Epense]","[Expenses]","[Expense Type] = ""All ...

Accounting for change in data over time
Please help, I'm having a brain freeze today, and I can't figure out what should be obvious. I have three tables: tblOrgs that holds information about an organization tblContacts that holds information about the peple that work at organizations tblMarketingRecords that hold marketing information about the organizations and the contacts that have been made. I need to account for two things that can change over time: the first is the person's title; the second is their association with the organizaion. In the first case, a person's title was Admin and is now Associate. ...

Discretionary X-axis (for date & time)
Hi, Im trying to plot some stock price data in a graph but running into the following problem: The data i want to plot is non continuous, i.e. the datetime should stop at 5.30 pm and continu on 9 pm the next day. When i try to do that with a scatter or line graph, it does not work. I guess one workaround would be to make an additional series that translates the date & time to e.g. 1, 2, 3 ,etc leaving out the dates& times i dont want to show. Tried this and it seems to work except for the fact that the interval between my datapoints isnt always the same. This makes the scaling...