Subtracting military times (decimal format)

I apologize if this has been asked & answered many times before, bu
after reading for days in this forum, I can not find a direct answer t
my needs.

I have timecards that are already displayed in military/decimal format
I do not want to convert to hours/minutes and I'm not interested i
displaying results in hh:mm format. I do not want the date displayed. 
simply want to calculate each employees total time worked for thei
shift's "start" to "end" (keeping the decimal format).  Results are no
rounded...employees are paid for all fractional hours. There are no se
shifts, employees start/end at all times throughout a 24 hour tim
period. Some employees clock in/out for meals, others work straigh
through.

Examples are below, and if I've calculated correctly via calculator
the result that should be displayed in Column E for each employee i
also indicated...

Formatted as text:

Column A is "Start" (shift begin)
Column B is "Out" (lunch)
Column C is "In" (lunch)
Column D is "End" (shift end)
Column E is Total Time

Employee 1: (value in E1 should = 8.39 hours)
A1=21.92
B1=2.00
C1=3.00
D1=7.31
E1=

Employee 2: (value in E2 should = 4.01 hours)
A2=10.00
B2= (blank - no break)
C2= (blank - no break)
D2=14.01
E2= 

Employee 3: (value in E3 should = 7.98)
A3=20.00
B3=00.00 (midnight -- not blank)
C3=00.99
D3=04.97
E3=

When I think I have the formula correct, it will work correctly on som
employees but not others.  At this point I am totally brain dead....
can't remember what I've tried and what I haven't.  I'm sure this is 
simple calculation that I am just trying to make too complicated...

Thanks in advance!

--
dlan
-----------------------------------------------------------------------
dlang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1632
View this thread: http://www.excelforum.com/showthread.php?threadid=27719

0
11/10/2004 9:16:00 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
535 Views

Similar Articles

[PageSpeed] 47

Hi

not sure i've got this right (i'm going cross-eyed with it too) but does

=IF(A1>D1,24+D1-A1,D1-A1)-(C1-B1)

work?

Cheers
JulieD


"dlang" <dlang.1fixnb@excelforum-nospam.com> wrote in message 
news:dlang.1fixnb@excelforum-nospam.com...
>
> I apologize if this has been asked & answered many times before, but
> after reading for days in this forum, I can not find a direct answer to
> my needs.
>
> I have timecards that are already displayed in military/decimal format.
> I do not want to convert to hours/minutes and I'm not interested in
> displaying results in hh:mm format. I do not want the date displayed. I
> simply want to calculate each employees total time worked for their
> shift's "start" to "end" (keeping the decimal format).  Results are not
> rounded...employees are paid for all fractional hours. There are no set
> shifts, employees start/end at all times throughout a 24 hour time
> period. Some employees clock in/out for meals, others work straight
> through.
>
> Examples are below, and if I've calculated correctly via calculator,
> the result that should be displayed in Column E for each employee is
> also indicated...
>
> Formatted as text:
>
> Column A is "Start" (shift begin)
> Column B is "Out" (lunch)
> Column C is "In" (lunch)
> Column D is "End" (shift end)
> Column E is Total Time
>
> Employee 1: (value in E1 should = 8.39 hours)
> A1=21.92
> B1=2.00
> C1=3.00
> D1=7.31
> E1=
>
> Employee 2: (value in E2 should = 4.01 hours)
> A2=10.00
> B2= (blank - no break)
> C2= (blank - no break)
> D2=14.01
> E2=
>
> Employee 3: (value in E3 should = 7.98)
> A3=20.00
> B3=00.00 (midnight -- not blank)
> C3=00.99
> D3=04.97
> E3=
>
> When I think I have the formula correct, it will work correctly on some
> employees but not others.  At this point I am totally brain dead....I
> can't remember what I've tried and what I haven't.  I'm sure this is a
> simple calculation that I am just trying to make too complicated...
>
> Thanks in advance!!
>
>
> -- 
> dlang
> ------------------------------------------------------------------------
> dlang's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=16329
> View this thread: http://www.excelforum.com/showthread.php?threadid=277192
> 


0
JulieD1 (2295)
11/11/2004 5:24:16 AM
If all times were on the same day, the formula is simply:

=d1-c1+b1-a1

But when the times span midnight, you need to add 24 hours to the formula. 
So use:

=d1-c1+b1-a1+if(d1<a1,24,0)

-- 
Regards,
Fred
Please reply to newsgroup, not e-mail


"dlang" <dlang.1fixnb@excelforum-nospam.com> wrote in message 
news:dlang.1fixnb@excelforum-nospam.com...
>
> I apologize if this has been asked & answered many times before, but
> after reading for days in this forum, I can not find a direct answer to
> my needs.
>
> I have timecards that are already displayed in military/decimal format.
> I do not want to convert to hours/minutes and I'm not interested in
> displaying results in hh:mm format. I do not want the date displayed. I
> simply want to calculate each employees total time worked for their
> shift's "start" to "end" (keeping the decimal format).  Results are not
> rounded...employees are paid for all fractional hours. There are no set
> shifts, employees start/end at all times throughout a 24 hour time
> period. Some employees clock in/out for meals, others work straight
> through.
>
> Examples are below, and if I've calculated correctly via calculator,
> the result that should be displayed in Column E for each employee is
> also indicated...
>
> Formatted as text:
>
> Column A is "Start" (shift begin)
> Column B is "Out" (lunch)
> Column C is "In" (lunch)
> Column D is "End" (shift end)
> Column E is Total Time
>
> Employee 1: (value in E1 should = 8.39 hours)
> A1=21.92
> B1=2.00
> C1=3.00
> D1=7.31
> E1=
>
> Employee 2: (value in E2 should = 4.01 hours)
> A2=10.00
> B2= (blank - no break)
> C2= (blank - no break)
> D2=14.01
> E2=
>
> Employee 3: (value in E3 should = 7.98)
> A3=20.00
> B3=00.00 (midnight -- not blank)
> C3=00.99
> D3=04.97
> E3=
>
> When I think I have the formula correct, it will work correctly on some
> employees but not others.  At this point I am totally brain dead....I
> can't remember what I've tried and what I haven't.  I'm sure this is a
> simple calculation that I am just trying to make too complicated...
>
> Thanks in advance!!
>
>
> -- 
> dlang
> ------------------------------------------------------------------------
> dlang's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=16329
> View this thread: http://www.excelforum.com/showthread.php?threadid=277192
> 


0
fredsmith99 (155)
11/11/2004 5:29:11 AM
Reply:

Similar Artilces:

Help needed changing date formats from m/d/y to d/m/y
I have a spreadsheet with a column of dates in the format ddd m/d/yyyy, e.g. "Mon 9/29/2003". The data has come in as text as I have cut and pasted it from another source. I sort of have something working, but it only works for dates with 2 digits (i.e. from October (10th month) on and from the 10th of each month on), e.g. "Wed 29/10/2003". The formula I have is: =DATE(RIGHT(E3,4),MID(E3,6,2),MID(E3,9,2)) Can anyone help me get this to work for all dates? Thanks in advance. Craig An alternative: Select your column. Choose Data/Text To Columns. Select the Delimited ...

SQL Statement to Update ReorderPoint based on QTY Sold During time
I am using RMS 1.3 with SQL 2005 SQL Statement to Update ReorderPoint based on QTY Sold During certin time frame. 2weeks, 3weeks, Month, 2 Month We sell unique products where reorder level changes every 2-3 months. We want to run a SQL query and update item table with ReorderPoint and ReorderQTY based on QTY sold during specific period. Please review this SQL Statement SELECT ItemLookupCode, ReorderPoint, SUM(lastweek.Quantity) as LastWeek, SUM(lastqy.Quantity) as LastQY FROM Item LEFT JOIN TransactionEntry l...

Time Sheet
I have a time sheet with four fields: Time In (C2), Lunch Out (D2), Lunch In (E2), Time Out (F2) I'm trying to create a formula in cell G2 to calculate the regular hours and another formula in H2 for the overtime hours, but have been unsuccessful. Current fields are formatted as Number | Custom | h:mm AM/PM for C2 thru F2 and are formatted as Number | Custom | h:mm for G2 and H2. Can anyone help me. Hi, Look into CPearson Web it has all the working with overtime Hs http://www.cpearson.com/excel/overtime.htm "Kathy" wrote: > I have a time sheet wi...

Format Column
I have a column with inmate id numbers in it. I imported them from a text based program. I made a custom formatting for the column because all the id numbers begin with zero. IE 00112356 or 01555666 etc... The format I used was 00000000 under custom. The problem now is that I want to import them into access but access doesn't see the zero at the beginning. How can I make it show the literal number including the zeros? I tried adding the '01222555 before the number, but I would have to manually append 2000 records. Is there a faster way? Morph. Hi you could use a helper column...

Hide formatting marks
Would anyone be able to advise how I get the formatting marks to be hidden in my return e-mails that I reply to? Please advise Thanks! when you reply to your mail, goto tools-->options-->view and under formatting marks, deselect whichever you dont want to see. regards, Subbu. --- Subramanian .S v-subs@online.microsoft.com Microsoft GPS This posting is provided "AS IS" with no warranties, and confers no rights. ...

Excel automatically changes the formatting of the cell to "Time"
Hello, I have a long column of numbers [dates in the YY:DD format]. I wanted to replace ":61" to ":59". Even though the cells are initially formatted as Text, as soon as I make the change, Excel changes the formatting to Time, and the cell with the change now has text ":59:00" in it. Is it possible to force Excel to keep the cells formatted as Text? Thank you! Sam, If the cells are truly formatted as text (Format - Cells - Number - Text), the formatting should never change, and you should always see exactly what you've typed. Give us an example...

Cell will not format
Right click on cells that were copied over from another excel sheet and can not format the cell - right click - format does not work Check whether the Worksheet is protected. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "jeanngoodyear" wrote: > Right click on cells that were copied over from another excel sheet and can > not format the cell - right click - format does not work Right-click>format cells does not show up or you cannot re-format the data? What type of data are in the cells? Maybe dates ...

Formatting of email returned.
Hi there, I sent the email below in HTML format using outlook 2007. This email bounced and was sent back to me. I received it in the format below. Why does it appear in this format, where the formatting is all messed up. What are all these characters below. =20. Also look at the subject line below: Subject: =?windows-1250?Q?Raj_Tyagi_- _Work_Order_=28Please_send_confirmation=85=29?= =?windows-1250?Q?_Thank_you.?= Why does it appear like this? What is the solution? Thanks... Ken ---------------------------------------------------------------------------------------------------------------...

NAICS Code Cell Format
Hello, I recently downloaded some US Census data (NAICS codes) into Excel and they have a cell format that I am unable to change. When I sort a list of numbers (e.g. 10, 12, 101, 111, 112), rather than sorting these numbers from lowest to highest (or vice versa), the numbers are sorted as follows: CURRENT SORT 10 101 111 112 12 DESIRED SORT 10 12 101 111 112 The list is being sorted as if the numbers have a hidden decimal after the first two numbers. I have tried to altering the number format to no avail. Does anyone have a suggestion for how I can change the cell format so that the number...

why no video format for my windows media creating dvd
my creating dvd has only data format; have seen some references to video format as well. this prevents me from copying movies, tv shows to a dvd that can then be shown on another dvd player "webbnc" <webbnc@discussions.microsoft.com> wrote in message news:6A5FE37C-8D1C-42D1-A095-4C34BD721155@microsoft.com... > my creating dvd has only data format; have seen some references to video > format as well. this prevents me from copying movies, tv shows to a dvd > that > can then be shown on another dvd player Your question may be better answered in a news...

csv format is wrong in Denmark
When I make csv file from excel 2007, then I do not get a comma-separated file. The cells are separated with a semicolon and the the normal dot in numbers is a comma. This is the case when you live in Denmark. The problem with the "Danish" excel csv format is that we can not export it to what ever we want. If I want to use it in Microsoft Visual Studio Team System, for a unit test, then it does understand the format. Is it possible in Office to save in correct cvs format? This is something to be done with the Windows setting. Start>Settings>Control Panel>...

If Statement with Time
I am trying to write IF THEN Statement using time numbers (i.e. 16:00, 13:30, 6:30). It doesn't appear to be working. Here is a sample =IF(AND(E4<17,E4>4),"NP", "P") where E4 is a cell that contains the military time. I keep only getting a value of P returned even when it should be NP. I tried using 17:00 and 4:00 in the statement and got an error -- Court Hi try =IF(AND(E4<17/24,E4>4/24),"NP", "P") or =IF(AND(E4<TIME(17,0),E4>TIME(4,0)),"NP", "P") -- Regards Frank Kabel Frankfurt, Germany "Co...

Time-Line Chart
I'm trying to create a "time-line" chart on both sides of the X axis. For example, item '1' lasts (3) seconds long and is 'positive' (above X axis). Item '2' lasts (2) seconds and is 'negative' (below X axis). Item '3' lasts (5) seconds and is 'positive'. Every time I graph this the lengths of the items are graphed vertically instead of horizontally. Any suggestions? *** ***** _________________ ** Thanks, Zack Hi, Zack, Try using a bar chart as the chart type. (Note: If you don't want your timeline...

Time Required to Save a File
I have a large Excel file that takes about 15 seconds to save if I have two other files open that the first file is linked to. If I do not have these two other files open it takes several minutes to save the first file. Does anyone know what is causing this, and if it can be fixed? I do not always remember to open the other two files so I am frequently stuck waiting for the first file to save. Thanks, John Do you have calculation set to manual? There's an option to recalculate before you save. Tools|options|calculation tab If you have manual calculation set, you could turn off ...

Custom number format button
I'd like to create a toolbar button linked to a custom number format so that clicking on the button would re-format the active cell or range to my custom format. Hi Ken, Alt-F11 to enter the VBE Insert | Module Paste the following code: '===========>> Public Sub aTester() Selection.NumberFormat = "#,##0_);[Red](#,##0)" End Sub '<<=========== Alt=F11 to return to Excel View | Toolbars | Customize | Select 'Commands' tab Select 'Macros' in the left-hand Categories window Drag the smiley icon ftom the commands window to your toolbar Ri...

Using formatted cells in formulas
I have been provided a worksheet with a list of 5-digit part numbers that the creator of the worksheet formatted as zip code to preserve leading zeros. I need to use these cells in a formula which uses the "&" operator to tack on a suffix. For example: Original part number cell A1 = 2345 (with zip code formatting displays as 02345) I need cell A2 to be 02345XX, so I'm using the following formula: A1&"XX". Problem is, when I do this, Excel drops the leading zero and displays 2345XX. Is there a way to have Excel use the formatted display string for cell A1 inst...

Why do I get this ###### instead of 11:00 p.m. in formatted cell?
One reason could be that the column isn't wide enough. Try widening the column. -- Biff Microsoft Excel MVP Always supply your formula and the values of the input cells -- Kind regards, Niek Otten Microsoft MVP - Excel "new user" <new user@discussions.microsoft.com> wrote in message news:32404C0C-FB66-4D90-A301-42D0322E2CFE@microsoft.com... | On 15 =F1=E5=EF, 19:43, new user <new u...@discussions.microsoft.com> wrote= : > Not to forget: if cell contains fomula that evaluates time value and result turns to be negative, it will be shown as ###### no...

cell formatting #7
I have sheet that is not letting me format a number unless I doubl click on the cell. Say the cell value is 5.00000 I cannot reduce the number of decimals. There is over 12k rows of data so I don't particularly want to g through each cell. Thanks in advanc ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Try - tools / options / edit / fixed decimal. >-----Original Message----- > >I have sheet that is not letting me format a number unless I double >clic...

Sending HTML format from Exchange 2007
I am looking for a way to force all messages sent from exchange to go out in an HTML format. We are adding some branding info to outgoing email and it needs to be in HTML format. There are several clients using exchange, so the only logical place to manage things is on the server. Looks like any way to do this may be well hidden. Any suggestions? Thanks, Manuel, I believe that can only be done on the email client. "Manuel" <Manuel@discussions.microsoft.com> wrote in message news:41986B58-3FF5-4738-9DF8-84CF94537E0E@microsoft.com... >I am looking...

Chart-date axis format
I am using date-times as the x axis. The differences between two point is not always the same. Could be 3 days, 8 days etc. But the data axis formats are in day order day 1, 2, 3, 4 etc.. I can't seem to modify teh axis format correctly to get what I need. What format option can I set so that if I have 3 data points on day 1, 5 and 15 that I have 3 dates on the x axis instead of the 15 I get by default. Thanks in advance for any hints/help Right click the chart; select Chart Options On the Axes tab; chance the Primary axis from Automatic (or Time Series) to Category best wishes --...

Help with Time Input
Hello, I am working on building a spreadsheet that would enable the user to easily select the specific time they are looking for using the format MM/DD/YYYY 00:00:00 AM/PM. Does anyone have any examples or suggestions on automating this task, such as a macro or drop down menus for easily selecting the input? Thank you, Mark Huh? Surely you don't mean a drop down for every second of every day from Jan 1st 1900 to eternity. I don't know what you are really looking for but it would be faster to just type in the date and time than to go through dropdowns for each of year/mo...

hours from job-sheet to time-sheet
We have a sheet titled Job-Sheet. In column A1-A20 there's days of a week. In column B1-B20 there's names. In column C1-C20 there are hours. MON Anderson, Josh 8 MON Bailey, John 8 MON Blakely, Brett 8 TUE Eddings, Cindy 8 TUE Floyd, Bill 8 TUE Anderson, Josh 8 WED Bailey, John 9 WED Blakely, Brett 9 WED Eddings, Cindy 9 THUR Anderson, Josh 10 THUR Bailey, John 10 FRI Anderson, Josh 10 SAT Anderson, Josh 9 SAT ...

Run time error 3077
I am getting a run time error 3077 whenever I select a record from the combobox with an apostophe as part of the text in the combo box. Can anyone tell me what the apostrophes do in the code below and if there is a good error handling feature to prevent this so I can have company names with apostrphes in the combo box without getting the run time error? Listed below is the code: Private Sub CboCompanyLookup_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Client Name] = '...

Windows Installer loads every time I open Excel 2007
Hi all, I'm facing the following problem: when I open Excel 2007 the windows installer loads every time... is not going to give me any error, but it's something annoying... does anyone has any solution??? thanks Vit p.s. this happens also with other office 2007 applications Try clicking "Help" and then "Detect and Repair..." That worked for me when I had the same problem. ...

Simple Macro to copy each row in the database "X" times? (Excel 2002)
This is a multi-part message in MIME format. ------=_NextPart_000_000D_01C3ECA5.0E42E590 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I was wondering if someone could help me with a macro to cut down on = some repetitive task activity. I have a regularly increasing DB of address information in Excel with = several hundred entries with row 1 as the header row. Depending on the = demand, we have to create mail labels in various quantities per address, = consecutively. I foresee a macro that when kicked off will prompt the = user to ente...