Time sheet date question

[chapm4]

Need help with a formula.

Work uses a time sheet that looks like the following:

Week Ending: _________

DAY DATE ON OFF
SUNDAY
MONDAY
TUESDAY 2/1/2005 8:00 4:00
WEDNESDAY 2/2/2005
THURSDAY 2/3/2005
FRIDAY 2/4/2005
SATURDAY 2/5/2005

I now enter correct date for the new month in the week ending field then calculate the correct date by entering the week ending date in Saturdays cell and then subtracting 1 for each of the cells going up. As you can see in this example, the month starts on Wednesday (2/1/2005). How can I also use a formula in this case for Sunday and Monday but leave them blank if they are dates in the previous month.

[Domenic]

Assuming that A1 contains your 'Week Ending' date, and that Column A contains your 'Day' starting at A3, try the following...

B3, copied down:

=IF(MONTH($A$1-(9-ROW()))=MONTH($A$1),$A$1-(9-ROW()),"")

Hope this helps!

Need help with a formula.

Work uses a time sheet that looks like the following:

Week Ending: _________

DAY DATE ON OFF
SUNDAY
MONDAY
TUESDAY 2/1/2005 8:00 4:00
WEDNESDAY 2/2/2005
THURSDAY 2/3/2005
FRIDAY 2/4/2005
SATURDAY 2/5/2005

I now enter correct date for the new month in the week ending field then calculate the correct date by entering the week ending date in Saturdays cell and then subtracting 1 for each of the cells going up. As you can see in this example, the month starts on Wednesday (2/1/2005). How can I also use a formula in this case for Sunday and Monday but leave them blank if they are dates in the previous month.

[chapm4]

That works great, thanks! The first Monday-Sunday (Cells B4-B10) are correct. Names of the days of the week are in column A, but I changed your formula to B. The problem is that the sheet then skips down to B15 to start the next 7 days. The formula then assumes that the first day is 2/11/2005 when it should be 2/7/2005.

I know this is screwey and would be very easy to rewrite the sheet, but it is one the whole school district uses and I can't redo it.

[Domenic]

Try the following...

B3, copied down to B9:

=IF(MONTH($B$1-(CELL("row",$B$9)-ROW()))=MONTH($B$1),$B$1-(CELL("row",$B$9)-ROW()),"")

B15, copied down to B21:

=$B$1+(ROW()-ROW($B$15)+1)

Hope this helps!

That works great, thanks! The first Monday-Sunday (Cells B4-B10) are correct. Names of the days of the week are in column A, but I changed your formula to B. The problem is that the sheet then skips down to B15 to start the next 7 days. The formula then assumes that the first day is 2/11/2005 when it should be 2/7/2005.

I know this is screwey and would be very easy to rewrite the sheet, but it is one the whole school district uses and I can't redo it.

[chapm4]

Ithink that would work, but B15 is coming up with the same date as B10 and since I can't seem to grasp what your formula is doing I can't figure out how to fix it.

Can you help one more time, or explain what the formula is doing?

Thanks very much,
Dave

[Domenic]

Can you confirm how your data is laid out? For example...

1) Which cell contains the 'Week Ending' date?

2) Which cells contain the 'Day'?

Ithink that would work, but B15 is coming up with the same date as B10 and since I can't seem to grasp what your formula is doing I can't figure out how to fix it.

Can you help one more time, or explain what the formula is doing?

Thanks very much,
Dave

[chapm4]

Let's see.....

I have attached a snapshot that will show the first couple of weeks.

[Domenic]

The reason the wrong date was being returned is because B1 contained the wrong date. But having looked at the attached snapshot, you may want to try the following instead...

B4, copied down to B10:

=IF(MONTH($B$1-(CELL("row",$B$10)-ROW()))=MONTH($B$1),$B$1-(CELL("row",$B$10)-ROW()),"")

B15, copied down to B21:

=IF(MONTH($B$12-(CELL("row",$B$21)-ROW()))=MONTH($B$12),$B$12-(CELL("row",$B$21)-ROW()),"")

B26, copied down to B32:

=IF(MONTH($B$23-(CELL("row",$B$32)-ROW()))=MONTH($B$23),$B$23-(CELL("row",$B$32)-ROW()),"")

B37, copied down to B43:

=IF(MONTH($B$34-(CELL("row",$B$43)-ROW()))=MONTH($B$34),$B$34-(CELL("row",$B$43)-ROW()),"")

B48, copied down to B54:

=IF(MONTH($B$45-(CELL("row",$B$54)-ROW()))<>MONTH($B$45),$B$45-(CELL("row",$B$54)-ROW()),"")

I'm assuming that to get the last two days of February only, without including March, you'd enter 3/5/05 in B45, giving you the following...

Sunday 2/27/05
Monday 2/28/05
Tuesday
Wednesday
Thursday
Friday
Saturday

Hope this helps!

Let's see.....

I have attached a snapshot that will show the first couple of weeks.

[chapm4]

That works perfect, thank you very much.

Dave

[chapm4]

I really do appreciate the help, so while we are on the subject.....

I want to put in the standard 8 hours if that was a date listed in the B column so:

If B5 contains a date (is not null), then G5 should contain 8 hours. I wouldn't put a formula in G4 or G10 as they are Saturday and Sunday respectivelly.

[Domenic]

Try...

=IF(N(B5),8,"")

Hope this helps!

I really do appreciate the help, so while we are on the subject.....

I want to put in the standard 8 hours if that was a date listed in the B column so:

If B5 contains a date (is not null), then G5 should contain 8 hours. I wouldn't put a formula in G4 or G10 as they are Saturday and Sunday respectivelly.