Need help to create timecard

[makik4]

I am modifying timecard which was created by someone else last year.
When I entered starting date, it automatically shows all other dates
under the correct day of the week. For some reason, starting from
August, that function was showing 2007 information. For instance,
when I entered 9/1/08, it appeared under Saturday, (9/1/07 was
Saturday), even though it should be appeared under Monday.

This is the formula in the cell for starting date, where I put down
the date.
=DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(NOW())<16,1,16))

This one is for ending date, which automatically appears when I enter
starting date.

=DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(NOW())<16,15,IF(OR(MONTH(NOW())=4,MONTH(NOW())=6,MONTH(NOW())=9,MONTH(NOW())=11),
30,IF(MONTH(NOW())=2,IF(MOD(YEAR(NOW()),4)=0,29,28),31))))

This one is for the date in timesheet, which automatically appears
when I enter starting date.

=IF(OR(MONTH($L$1)=1,MONTH($L$1)=4),IF(DAY($L$1)=16,DATE(YEAR($L
$1),MONTH($L$1),DAY(16)),IF($B$3="","",1+$B$3)),IF(MONTH($L
$1)=8,IF(DAY($L$1)=1,DATE(YEAR($L$1),MONTH($L$1),DAY(1)),IF($B$3="","",
1+$B$3)),IF($B$3="","",1+$B$3)))

I hope this information will be good enough. Thank you very much in
advance.

 

[ShaneDevenshire]

Hi,

Before I try my luck at your formula, what should it be returning?

As an aside, you can simplify the second formula to read:

=DATE(YEAR(NOW()),MONTH(NOW()),IF(DAY(NOW())<16,15,DAY(EOMONTH(NOW(),0))))
To use EOMONTH in 2003 or earlier you must attach the Analysis ToolPak. You
do this by choosing Tools, Add-ins and checking Analysis ToolPak.

 

[Dave Mills]

The first two formulae do not seem to refer to any input data, they just depend
upon the now() function so I do not understand your statement "where I put down
the date" or "entering the start date"

The third has reference to other cells. Check that the dates in those cells are
2008 and not 2007. I have seen this sort of thing where the cell format is
"ddMM" instead of "ddmmyyyy" and you fail to notice that the value actually sets
you up in the previous year.