Calculating Variables

[K. Grass]

Hi guys,

In Excel 2003, my workbook is set up to calculate the last six days on duty
as those hours are entered in each cell corresponding with each date of the
month and then subtract from 60:00 hours to come up with the available hours
for the next day.

Cell A4 adds the previous 6 days in that column
Cell A5 then subtracts that number from 60:00 hours

For example;

A1 A2 A3 A4
A5
Drive On Duty Total Last 6 Days
Available Tomorrow
04:32 10:00 14:32 32:00
28:00

The Variable
I need cell A5 to reset to 60:00 hours if the values are zero in the past
34:00 hours. Is this possible?

I hope I said all that right ;-)

TIA,
K. Grass
mailto:[email protected]

 

[John]

I'm not sure if you days of the month are down rows or
accross columns. But here a two possibilites. I am
asuming you are working 6 10 hour days. If the data is in
rows then use this =IF(AND(D5<5,SUM(D68)=0),60,(60-D10))
If day one is in cell D3 and day two in D4 then D5 would
need more than 4 hours worked to stay in the range of the
last 34 hours. Any hours in cell D5 greater then 4 and
any hours in cell D6,D7 and D8 will subtracted the hours
worked from 60 hours. If D5 is less than 5 hours and
D6,D7, and D8 are zero, then 60 will appear.
If your data is across columns then use:
=IF(AND(H3<5,SUM(I3:K3)=0),60,(60-K10))

Did I answer you question?

 

[K. Grass]

Hi John,

Forgive me, but the original formatting of cells was simplified for reader
purposes. But I can see that this is going to be a little more involved.
So...

The days of the month are formatted down columns A1 through whatever cells
are needed for the days of that month.

Cell E6 is the Drive Time. Cell F6 is the On Duty hours. Cell G6 contains
the formula; =SUM(E6:F6). Now, cell I6 adds that total and the previous 5
cells in the G column to come up with a total (the last six days) on duty.
Cell K6 is the constant of 60:00 hours which is; ="60:00"-I6 to come up with
the available hours for the next day. Column K is the Available Tomorrow
hours column.

If it wasn't considered rude I'd attach the file so you can see exactly how
it works, but I won't do that without a specific request.

So what exactly I need is column K to calculate the available hours tomorrow
with the caveat that it will reset to 60:00 if the value of the preceding
cells in the G column are 0:00 in the past 34:00 hours.

I'm currently experimenting with VBA modules in the hopes that I can come up
with something to solve this dilemna. I'm also using Microsoft Press' Excel
version 2002 Inside Out book as an aid.

Any input is greatly appreciated John, TIA

K. Grass
mailto:[email protected]