Employee schedule: I want it to calculate time entered such as 11.

I would like to enter the in and out times for each employee on my schedule 
spreadsheet in normal format (e.g. 11:00pm - 7:00am) and have it calculate 
how many hours the emploee worked.   Everything works well until I have an 
employee who works the night shift.  The subtraction of 11pm to 7am gives me 
a negative and incorrect anser.  How can I achieve this and still use normal 
format times as above?

Rudy
0
Rudy (18)
1/5/2005 11:53:04 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
655 Views

Similar Articles

[PageSpeed] 42

If you put the date (time in) in column A for example, and the time in 9:00 
PM in column B, then in Column C, you can input the forula =A1+B1 (formatted 
for date and time). 

Then do the same thing for time out in a few more columns.  Use the complete 
data (date and time) cells for subtraction and you won't get the error.  You 
can always hide the cells holding the redundant data if they get in the way.



"Atlanta Rudy" wrote:

> I would like to enter the in and out times for each employee on my schedule 
> spreadsheet in normal format (e.g. 11:00pm - 7:00am) and have it calculate 
> how many hours the emploee worked.   Everything works well until I have an 
> employee who works the night shift.  The subtraction of 11pm to 7am gives me 
> a negative and incorrect anser.  How can I achieve this and still use normal 
> format times as above?
> 
> Rudy
0
Beverly76 (3)
1/6/2005 12:57:01 AM
Hi!

A1 = 11:00 PM
B1 =  7:00 AM

=B1-A1+(A1>B1)

Biff

>-----Original Message-----
>If you put the date (time in) in column A for example, 
and the time in 9:00 
>PM in column B, then in Column C, you can input the 
forula =A1+B1 (formatted 
>for date and time). 
>
>Then do the same thing for time out in a few more 
columns.  Use the complete 
>data (date and time) cells for subtraction and you won't 
get the error.  You 
>can always hide the cells holding the redundant data if 
they get in the way.
>
>
>
>"Atlanta Rudy" wrote:
>
>> I would like to enter the in and out times for each 
employee on my schedule 
>> spreadsheet in normal format (e.g. 11:00pm - 7:00am) 
and have it calculate 
>> how many hours the emploee worked.   Everything works 
well until I have an 
>> employee who works the night shift.  The subtraction of 
11pm to 7am gives me 
>> a negative and incorrect anser.  How can I achieve this 
and still use normal 
>> format times as above?
>> 
>> Rudy
>.
>
0
biffinpitt (3172)
1/6/2005 5:19:41 AM
Reply:

Similar Artilces:

How to reinstall or repair Windows Media Player 11
My Windows Media Player 11 is broken. It sounds horrible. The sound is garbled and no amount of adjustments change this. Previous attempts to get help fixing this have been unsuccessful. And now, if I burn MP3s with it, to a CD, the CD will not play in stand-alone players. It gives a No Disc message, though I can see that the disc has been burned. So... How about uninstalling it then reinstalling? Repairing it? Rolling back to an older version? I found these instructions: http://www.microsoft.com/windows/windowsmedia/player/11/readme.aspx#RollingbacktoapreviousversionofthePl...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

Hello 04-11-10
Hello Haw do you do? ...

varible calculation
I am trying to create a calculation for haulage/trucking charges 1-300 kilos £ 30 300-400 kilos £ 35 400-500 kilos £ 40 What formula could I use to accomplish this?? -- kinsey assuming that you mean: 1-299 300-399 400- then try: =LOOKUP(A1,{0,300,400},{30,35,40}) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "kinsey" <kinsey@discussions.microsoft.com> wrote in message news:46C0E5C2-11A6-4AF7-9F2A-8D4D23F4498B@microsoft.com... &...

calculating return in a range
Hi, I have a range of stock prices. with indication for buy or sell. and i want to calculate the return of the range for example : buy 522.73 buy 527.9 buy 528.81 buy 532.33 buy 535.1 buy 536.23 buy 537.03 buy 538.57 sell 537.54 sell 532.86 sell 538.14 sell 533.08 buy 531.22 I want to calculate the return for the first buy signal. the range is from the start to the first sell signal 537.54- 522.73 then the sell range untill the first buy signal 531.22- 537.54 and so on.... -- kman ------------------------------------------------------------------------ kman's Profile: http://www.e...

SUMIF function not calculating from closed worksheet
We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade each department would access a spreadsheet that would load data from a common lookup spreadsheet The loading process was performed as follows: The lookup file and each department file would be opened to update the link for that month and then saved and closed. When the department head would open the department file, a question would appear asking if they wanted to update the link, if they answered no the spreadsheet would open and display the calculated data. If they answered yes, the file would o...

Not allow entering repeated references in a column
Frank, If you don�t mind ... I will send it :) . As I don�t know your e-mail, I will give mine, which is ssouritinha@sapo.pt Just send your e-mail to my e-mail box and I will send the file. Thanks, Ritinh -- ritinh ----------------------------------------------------------------------- ritinha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1566 View this thread: http://www.excelforum.com/showthread.php?threadid=27195 ...

catching enter key for multiple editboxes
I know that to catch the enter key I need to subclass the editbox control, but I was wondering if there is a way to do this for multiple editboxes other than to subclass each control? I suppose I could do one and then create the others dynamically, but can it be done during design as well? Roger ...

Set default time zone for all users
Is there a way to make the time zone default to a certain value so that I don't have to go into every user and change it to the correct zone? No. Each user must change their own timezone settings. I have no idea why Microsoft do not pick up your computers timezone. Hopefully this will be changed in a version 2.0 release "Jack Black" <anonymous@discussions.microsoft.com> wrote in message news:6F5F4A10-B6AD-420C-9ACB-F73A13FDABB9@microsoft.com... > Is there a way to make the time zone default to a certain value so that I don't have to go into every user and change i...

Appointment times won't post correctly
On the calendar, appointments, meetings, etc. made or received show up on the date and time received or sent; regardless of the actual date or time of the appointment. Is there a setting that will correct this? I've exhausted myself trying to find a fix. Outlook 2003 There is a seperate calendar time zone setting that has to be set in addition to your system clock. Pain in the ass. Go to Options and then select Calendar. You should see the Time Zone button there. Set it accordingly, but be aware that everything in your calendar up to that point will change. "Keith Wilso...

office 2003 w/ windows 7 requires reacceptance ot T&C's each time
office 2003 w/ windows 7 requires reacceptance of T&C's each time i open work or excell or any office program. How do I get rid of this? Boot into Safe mode in Windows 7,open any Office app and then accept the terms -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "mouser830" <mouser830@discussions.microsoft.com> wrote in message news:E7B7602D-F5D8-4559-98EF-A2CCC1E2D6FD@microsoft.com... > office 2003 w/ windows 7 requires reacceptance of T&C's each time i op...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

Want to remove HQ?
Hello: My client has HQ installed and 2 stores running Store Ops. HQ has not been functioning properly, some bugs that are occurring include finance charges and customer payments posting to the wrong customer. Inventory changes made in HQ are not downloaded to the stores. They are also frustrated that tasks that were accomplished in one step in Store Ops e.g. changing prices and costs on an item, now require several steps and running two or more worksheets in HQ. They would like to remove HQ and run all three stores as a single store with addditional lanes. The stores will be conn...

Unable to enter correct product key
I have purchased a Mac G4 PowerBook and installed my Microsoft Office (student and teacher edition). Not understanding the significance of the Product Key, I entered a code that I have already used on another computer. Now I am unable to open Microsoft Word on my hew computer because I also have the same product key used on my old computer. I have three product keys. How can I install the program in my new computer using the correct product key? In article <d10c35c6.0408302116.39c1cc84@posting.google.com>, gstoddard@charter.net (george) wrote: > I have purchased a Mac G4 PowerBook ...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

why I see times new roman?
I have several html email messages composed in outlook express 6, I'm sure I highlited all text and set it to Arial 10. Then I drag the file to desktop, move it to vista windows mail draggin into inbox or other folder. The result is some parts of text shows arial 10 but some others Times new roman 10 or 12 what is annoying, because I use all arial 10, so I have to manually forward the email, highligh it again and set arial 10 and then, yes, I can see it properly. I checked the compose default font and everything is fine. Why is that? Is there any work around , helo...

time #11
I have set up a spreadsheet with time based on what our company truck drivers work. We work under a DOT rule of 70 hours / 8 days. That means that the driver can not work more than 70 hours in an 8 day period. The sheets I have (one for each month) goes back 7 days plus the current day and calculated how many hours a day the driver works and subtracts it from 70, leaving how many hours they can work the next day. If the calculation gets to 0 or above, the cell changes color to alert me that the driver's 70 hours are up and he/she has to have 34 hours off before he/she can be on duty ...

tasks entered in taskpad don't appear in task folder
All of the tasks that I have entered via the taskpad do not appear in the task folder, what am I doing wrong or do I have the wrong expectation? OL98/WinXP John Keith kd0gd@juno.com What view are you using on your Tasks folder? --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. "John Keith" <kd0gd@juno.com> wrote in message = news:6grrm0hkagessu2ecc0bj81pc41aarq39r@4ax.com... | All of the tasks that I have...

Employee - HR
I recently installed a SP against my test companies. After install, in the employee master, when I click on the Addiitonal Position button, I now see a screen labeled Employee Seat Assignments instead of the Employee Positions screen. I do see the Employee Positions screen in my Fabrikam company but not in some additional companies that I setup. Has anyone experienced this ? Do you know how I can set it back to the Employee Positions screen. Thanks !! ...

how do i rotate a 3 shift 200man schedule
like 80 people on two shifts and 40 on the third. i want to move small shift to day and slide half of day into swing and half of swing into graveyard. any suggestions? thanks in advance. First rotation: pick the first 40 people (by alphabetic order) from the larger groups to rotate to the next shift. Each subsequent rotation: move the group of 40 that did not move the last time. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <6F26D7D8-3A2F-4663-BE17-F0EF1AD26A3D@microsoft...

Times Subtraction Whith Access
Hi I need to know how to subtract between two times variants and to get a time result (With Access). Like this: VarTime=[time1]-[time2] 10:05:40=18:05:50-08:00:10 Thanks Access really isn't intended to do things like that, but ?Format(#18:05:50# - #08:00:10#, "hh:nn:ss") 10:05:40 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) <bennyzamir@gmail.com> wrote in message news:1175704084.566444.282820@b75g2000hsg.googlegroups.com... > Hi > > I need to know how to subtract between two times variants and to get a > time res...

Office 2004 SP 11.1.0 installation problems
I'm trying to update Office 2004 on my brand new iBook. I installed Office 2004 from the CD without incident. Then I downloaded SP 11.1.1.0 and 11.1.0 (which according to the MS website is to be installed 1st.) When I try to install, I received a message, "An error prevented the update from completing 11002:2,-14" The "Read Me" file indicates several potential explainatins/solutions none of whice are relevant except for uninstalling Office and then reinstalling it. I tried dragging the Office folder to the trash and reinstalled and am having the same problem. I've t...

prompting to save workbook every time (even when no changes made)
i have 3 workbooks all very similar and every single time i open any o them, even when not changing anything, i am prompted to save th thing. its so bad that i can literally open the workbook then click the clos button straight away and it STILL prompts me to save it because i seems to think something has changed. as far as i can see i dont have any macros or anything that will ru every time it starts and change something (i did have a = today in on cell which kept the date as today but taking this out made n difference -- Message posted from http://www.ExcelForum.com Hi Neowok! You prob...

Schedule+ free/busy trouble after additional Exchange in another s
Hi, Let me tell you about the situation.Recently we added an additional site to our 2000 based Domain and installed an additional Exchange 2000 Server there.There s no problem with the mail exchange.Trouble is with the calendar info of the users.We noticed a while ago that the availibility info of users in the remote site when trying to schedule a meeting from the main site was absent.Today we had the same problem with our users in the main site.Some of the users availability info is missing and the number of problematic user accounts is rising.As the screenshot in http://img293.images...

publisher 2002 #11
In publisher 97 you were able to ungroup clipart and recolor piece by piece and make it your own by using Microsoft Draw. How can I do this in Publisher 2002? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from addey <addeym2002@yahoo.com>... > In publisher 97 you were able to ungroup clipart and > recolor piece by piece and make it your own by using > Microsoft Draw. How can I do this in Publisher 2002? Click the picture Arrange > Grouping > Ungroup Click Yes on the dialog The tools that were previously provided by Microsoft Draw have been i...