adding up time over & under a specified amount

[BookWight]

Hi -

I have a caregiver who comes 3 times a week. There is a time limit of 38
hours per month for how long she's allowed to be here. I have a timesheet
set up to keep track of her hours. The usual time she's here each day is 3
hours. I've been trying to keep track of the over/under amount to know how
much time she has left for the month - would like help, please.

As a secondary matter, I would like to format the over 3 hours amounts,
when they occur, in red.

tia

 

[MyVeryOwnSelf]

I have a caregiver who comes 3 times a week. There is a time limit of 38

hours per month for how long she's allowed to be here. I have a timesheet
set up to keep track of her hours. The usual time she's here each day is 3
hours. I've been trying to keep track of the over/under amount to know how
much time she has left for the month - would like help, please.

Here's one way.

In column A, put the visit dates, and in column B put the corresponding hours for each date. The list starts in row 1 and should have no gaps.

In C1, put
=38-B1

In C2 put
=IF(A2="","",IF(A2>EOMONTH(A1,0),38,C1)-B2)
and copy C2 down past the end of the list.

The entries in column C should now equal the hours left in the month.

Modify as needed.

As a secondary matter, I would like to format the over 3 hours amounts,
when they occur, in red.

Select column B, then use conditional formatting. Use this equation
=B1>3
to select the affected cells. Click the "Format" button and pick the color red.

(I have Excel 2010.)

 

[MyVeryOwnSelf]

I have a caregiver who comes 3 times a week. There is a time limit of 38
hours per month for how long she's allowed to be here. I have a timesheet
set up to keep track of her hours. The usual time she's here each day is 3
hours. I've been trying to keep track of the over/under amount to know how
much time she has left for the month - would like help, please.

Here's one way.

In column A, put the visit dates, and in column B put the corresponding hours for each date. The list starts in row 1 and should have no gaps.

In C1, put
=38-B1

In C2 put
=IF(A2="","",IF(A2>EOMONTH(A1,0),38,C1)-B2)
and copy C2 down past the end of the list.

The entries in column C should now equal the hours left in the month. Negative numbers indicate being over the monthly limit.

Modify as needed.

As a secondary matter, I would like to format the over 3 hours amounts,
when they occur, in red.

Select column B, then use conditional formatting. Use this equation
=B1>3
to select the affected cells. Click the "Format" button and pick the color red.

(I have Excel 2010.)

 

[BookWight]

Wandering along the edges of microsoft.public.excel.worksheet.functions,
I found the following bit of electronic flotsam written by MyVeryOwnSelf

In column A, put the visit dates, and in column B put the
corresponding hours for each date. The list starts in row 1 and should
have no gaps.

In C1, put
=38-B1

In C2 put
=IF(A2="","",IF(A2>EOMONTH(A1,0),38,C1)-B2)
and copy C2 down past the end of the list.

The entries in column C should now equal the hours left in the month.

Modify as needed.

Thanks