Reset Sliding Window Calculation

I am having a tough time with a spreadsheet to calculate Driver's
Hours of Service. DOT rules allow a driver 70 hours in an 8 day
period.  Old spreadsheet would use simple sum for previous 8 days. 
New rules allow reset of hours to 70 if the driver has 34 consecutive
hours off.  The only data entry for each day is StartTime and OffTime.
 These are time formatted and the difference between the two is the
HoursWorked.  Also the difference between the OffTime and the next
StartTime is the HoursOff.  If the HoursOff is >= 34 the 70 hours is
reset.

I am having a tough time with a spreadsheet to calculate Driver's
Hours of Service. DOT rules allow a driver 70 hours in an 8 day
period.  Old spreadsheet would use simple sum for previous 8 days. 
New rules allow reset of hours to 70 if the driver has 34 consecutive
hours off.  The only data entry for each day is StartTime and OffTime.
 These are time formatted and the difference between the two is the
HoursWorked.  Also the difference between the OffTime and the next
StartTime is the HoursOff.  If the HoursOff is >= 34 the 70 hours is
reset.

			1/2/03	1/3/03	1/4/03	1/5/03	1/6/03	1/7/03
			Thu	Fri	Sat	Sun	Mon	Tue
Hours Available		45.00	36.00	27.00	27.00	70.00	62.00
Start time [Entry]	8.00	8.00			8.00	9.00
Off Time [Entry]	17.00	17.00			16.00	17.00
Hours Worked {Display}	9:00	9:00	0:00	0:00	8:00	8:00
Hours Worked {Display}	9.00	9.00	0.00	0.00	8.00	8.00
Consecutive Hr off (2)	14:00	15:00	7:00	31:00	55:00	17:00

I think I need to setup some type of sliding window to calculate the
hours worked in the previous eight (8) days and reset if consecutive
rest criteria is met (=>34 hours). Can I setup some type of dummy data
row containing hours worked and set the previous eight to zero and
then use this row to calculate hours used?  This would leave actual
hours worked data intact and represent new eight day window.

Any suggestions would be greatly appreciated.
Regards,

David
0
dlwilley (1)
12/16/2003 5:53:49 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
595 Views

Similar Articles

[PageSpeed] 18

Reply:

Similar Artilces:

my excel is no longer automatically calculating formula's
Excel 200 formula's no longer automatically calculating - have to enter each cell individually. Help please! ...

IF Stmt. to calculate cumulative commissions
Folks, I am trying to write an IF statement that will do the following: It calculates a commission amount based on 3 level of Sales: If Sales is < = $1,500, calculate 0.0025*1500 If Sales is < = $3,000, calculate 0.0045*3000 If Sales is > $3,000, calculate 0.0050* cell reference... Now, this may look easy enough..here's the trick that needs to happen Say, sales is $5,500..the commission calculated should be cumulative i.e. 0.0025 * 1500, + .0045*3,000 + .0050*(remainder amount) What would be a clean way of doing this? I will appreciate any help. Thanks. Regards, Shams...

Open new IE window when opening multiple FRx Web reports
When accessing FRx reports from the Webport the reports are opened up in an Internet Explorer window afetr double clicking on the report. If we then go back to the Webport and double click on a new report it is opened in the same Interent Explorer window as the first report. What is suggested is that when a second report is opened it displays in a new Internet Explorer window so that both reports can be viewed simultaneously. This is a suggestion from a customer who uses this tool extensively. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the sug...

Calculating cumulative percentage
I have a column of data with percentages of the total in the next column. I now want to add a third column that shows the cumulative percentage of the total. Something like: 1 2 3 A 10 10% 10% B 20 20% 30% C 30 30% 60% D 40 40% 100% What formula should I use to calculate the values for the third column? Thanks for your help. -- David One way .. In C1: =SUM($B$1:B1) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Prospect" wrote: > I have a column of data with percentages of the total in the next column. I > now want to add a ...

When to upgrade from Windows Mail to Outlook
I'm trying to determine whether I need to upgrade or can get better use out of Windows Mail. 1) Can I order my rules alphabetically to make them easier to search when I need to change them in Windows Mail? Is this feature offered in Outlook? 2) Can I set up Windows Mail so that all unread messages are visible in an Unread message folder as in Outlook? I can't find this feature in Windows Mail. 3) If I upgrade to Outlook, what's the best way to export/import rules so I don't have to write them from scratch? Please advise. 1) and 2) Those features are not ava...

On calculate question
Public Sub Impact2() If MsgBox("Would you like to record the changes that were just made?", vbYesNo, "Record Changes?") = vbNo Then Exit Sub End If End Sub Sorry, here is my question: In the "This Workbook" object I have a subroutine as follows: Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call Impact2 End Sub The subroutine Impact2 can be found below: Public Sub Impact2() If MsgBox("Would you like to record the changes that were just made?", vbYesNo, "Record Changes?") = vbNo Then...

Windows XP event 1000
Anyone know about the Windows XP 64 event 1000 and 1001? What is this? Way too little information for us to give you any help, if you want more help copy and paste the event to your next post. To copy the event double click on it to open it then click on the clipboard icon at the top right, the one below the up/down arrows. Other than that you can see if you can find anything useful here: http://www.eventid.net/search.asp John user@domain.invalid wrote: > Anyone know about the Windows XP 64 event 1000 and 1001? What is this? thanks for the clipboard tip. I was clicking...

Can you reset size of bar in scroll bar?
When working in a large Excel worksheet, the size of the scroll bar diminishes to allow you to scroll through the entire sheet. After intentionally deleting a large portion of the worksheet, the bar size on the scroll bar remains the same. Is there a way to reset the size of the bar on the scrollbar? You have to delete the rows (not just clear the contents), and then, the easiest thing is to save the workbook. -- Regards Juan Pablo Gonz´┐Żlez "vsholl" <vsholl@discussions.microsoft.com> wrote in message news:C4CF4E91-D282-4FB4-85EC-A19C5343F18A@microsoft.com... > Whe...

Re: How to adjust monitor screen in Windows 7?
How to adjust my monitor screen in Windows 7 - 64 bit version? My 22 inch LCD resolution setting is 1600x1024, but what should be a circle on the screen appeares as an vertical oval. What solution can mend this inconvenience. Many thanks for your help! Janine "Janine" <jiffy@live.nl> wrote in message news:33F5378F-FCF4-45F1-9127-3E0F277A8EF0@microsoft.com... > How to adjust my monitor screen in Windows 7 - 64 bit version? My 22 > inch LCD resolution setting is 1600x1024, but what should be a > circle on the screen appeares as an vertical oval. Wh...

backing up windows 7
I went into Windows 7 'Backup and Restore' function on my workstations and setup an incramental backup and scheduled it. I don't really want to run it via the scheduler, I want to be able to run it via a VBscript. Is this even possible? Thanks. ...

Calculating from last inputted cell
I am trying to write a formula to input the data from the last inputted cell in a range to another cell. Reading through some earlier threads I found a formula that works in 2 stages, the first finds the last inputted cell to return the cell reference, and the 2nd stage reuses the result, the formulas were =MAX(ROW($A$1:$A$200)*($A$1:$A$200<>"")) If the above formula was entered into cell B1 the 2nd formula would be entered in the cell where I wanted the data displayed and would be =INDEX($A:$A,B1) The 2 formulas work providing the entered data starts at row 1, my p...

Windows low on memory
Using Pub 2003 ... windows XP service pack 2 1.5 gig of ram 120 gig hard drive with only 45 gig used working with a pub file 280,000 kb size. I want to copy and paste the picture from one side of the page to the other ... but I get the error windows low on memory. Please help ...

Calculating age in years and months from Date of Birth
I think these formulas help me a lot, but I need the answer in years an months, not decimals or days! Can you help? To calculate the number of years from the date, enter the followin formula in cell B1: =YEAR(TODAY())-YEAR(A1) The result (rounded): 23 Years For a decimal solution, use the following formula =DATEDIF(A1,TODAY(),"m")/12 The result: 23.25 Years To calculate the number of months from the date, enter the followin formula in cell D1: =DATEDIF(A1,TODAY(),"m") The result: 279 Month -- Message posted from http://www.ExcelForum.com Hi try =DATEDIF(A1,NOW(),...

Windows NT Symbols Setup
I am looking for a little more info about the Windows NT Symbols Setup Tool. I think VC++ 6.0 install told me I needed to install this. I found a couple documents on support.microsoft.com. One of them states "Windows NT Debug Symbols must be set up if a computer needs to be remotely or locally debugged. It's not conclusive what I need this for: Just when I'm running the application on a Windows NT box, on any Windows Box, only under certain conditions I can compile apps with debugging and debug them just fine Why would I need to install this? ...

Control Windows 2003 CPU usage
Hi, Is there anyway to allocate CPU's to different apps? We have a Dell server with 2 Quad core CPU's, we want to give 1 Quad core (4 CPU's/cores) to one app and the rest to the server? Maybe the Windows System Resource Manager can help? http://www.microsoft.com/windowsserver2003/technologies/management/wsrm/default.mspx Best, Dajo Whiteford wrote: > Hi, > > Is there anyway to allocate CPU's to different apps? We have a Dell > server with 2 Quad core CPU's, we want to give 1 Quad core (4 > CPU's/cores) to one app and the re...

Locking windows xp pro while running & opening new session
Can you lock windows xp pro while a program is running (ie., motion detection software) & what could be the problem when the password is not asked when opening a new session ? Worked before but not now. I'm the administrator-user. Did enable section that requires all users to use password to open new session. -- seymour2 ------------------------------------------------------------------------ seymour2's Profile: http://forums.techarena.in/members/208039.htm View this thread: http://forums.techarena.in/windows-xp-support/1327306.htm http://forums.techarena.in ...

Windows Vista HP Administrator Account
Are there ANY risks involving unblocking the Admin account on Windows Vista HP, as long as you use a strong password? Sorry, this newsgroup is for questions about Access, the database product that's part of Office Professional. You'd be best off reposting your question to a newsgroup that deals with Vista. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Sc1234freak" <CorbinSchwalm@aol.com> wrote in message news:A7544A44-B79C-4B29-AC1F-C641478497C4@microsoft.com... > Are there ANY risks involving unblocking the Admin acco...

For Anyone using Windows 98 And Money 2005
Hello: Running the old W98 on a six year old Dell Dimension. Everything works pretty well, and am happy and content with W98. But, have just purchased MS Money 2005, and the startup screen says that W98 Second Edition (or W 2000, or XP) is required. It therefore won't install or run. Didn't even know there was a "second edition" of W98, although I did hear, of course of W 2000. Really don't want to have to run out and buy this W98 second edition, which I'm surprised is even still available. Everything else works, finally, just fine, and I sure don't want to ...

How to make a slide for a pricelist demo of the products
How I can make a demo slide for price list demonstration and to display in a plasma TV screen. Please anybody have an idea le me know. I want to display in repeated view of the pricelist . Please help With thanks Polachan Your question is very broad. What exactly do you need to know how to do? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "pol" <pol@discussions.microsoft.co...

Office 97 on Windows 98 compatability with Office 2007 on Windows
Could anyone advise is it possible to transfer the following Office 97 docs on an old computer running Win 98 to a new computer with Office 2007 running on Windows 7 - 1. Word 97, Excel 97 & Powerpoint 97 2. Outlook Express (v.6) e-mails & e-mail addresses Any advice appreciated ( by a dinosaur) Word 2007, Excel 2007 and PowerPoint 97 all have an option to save files in 97-2003 format, so it would be a pretty good guess that they can read files in that form. That it is running on Windows 7 should not make a difference. For OE you should ask in an OE newsgroup, as...

Arranque do computador com o Windows XP
Quando ligo o computador e aparace simbolo do windows XP, este fica parado, passa de seguida para outra imagem que diz : IDE scan, e de seguida vokta a tenatr iniciar windows XP , e repete-se tudo novamente. O que posso fazer? Boas! A mensagem de IDE Scan tipicamente é relacionada com a BIOS e não o sistema operativo. O processo de IDE Scan detecta algum disco? -- Boa sorte! Por favor responda para os newsgroups para todos partilharem. Ajude a ser ajudado, indicando toda a informação que seja relevante para a identificação do problema. Feed...

In Windows 7...desktop icon...*My Computer*????
In WindowsXP I had a desktop ion called "My Computer".... I used it a lot...... where is it in Windows 7? I was told to go there to see if I have 64 bit or 32 bit. Thanks Ron "ront2" <ront2@cox.net> wrote in message news:ODe$yGYyKHA.5040@TK2MSFTNGP02.phx.gbl... > In WindowsXP I had a desktop ion called "My Computer".... I used it a > lot...... where is it in Windows 7? I was told to go there to see if I > have 64 bit or 32 bit. Thanks Ron For answers to your Windows 7, use the Windows 7 support forums here: http://social....

Publisher for Windows 2000?
I just downloaded the free trial publisher software. However, when I tried to install it, a message informed me that I needed Windows XP or later. Do you have a free trial version for Windows 2000? -- DH No. Pub 2003 would have worked but you can no longer download a trial of that. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "David H." <DavidH@discussions.microsoft.com> wrote in message news:48A4E2BF-C8DC-4BDA-8E1C-00658BC5584F@microsoft.com... >I just downloaded the free trial publisher software....

money 2000 with windows xp?
can i use money 2000 with windows xp?? Yes. That's the combination I have and use with no trouble. David <anonymous@discussions.microsoft.com> wrote in message news:768a01c3e84f$9226c2a0$a101280a@phx.gbl... > can i use money 2000 with windows xp?? >-----Original Message----- >can i use money 2000 with windows xp?? >. >Hi, I tried money 1999 on XP and it works fine. I tried also Money 2000 Italien version on XP and also this was OK. By the way I am looking for a copy of Money 2000 English Version. Can you please read my post of Febr 1 2004 2:26 AM and may ...

Sony Viao n Windows Errorcode 0x8007005
Hi. for the love of God, King and Country can anyone help this simpleton! I have spent 3 whole days trying to install Windows security update KB969947/936929 on my Sony Viao laptop. Naturally I am getting the error 0x8007005. -- AnSapphireGael ------------------------------------------------------------------------ AnSapphireGael's Profile: http://forums.techarena.in/members/155910.htm View this thread: http://forums.techarena.in/windows-update/1272643.htm http://forums.techarena.in "AnSapphireGael" <AnSapphireGael.41uufb@DoNotSpam.com> wrote in messa...