Leap Year

[Roger]

I have a time report sheet I have made in Excel. Our pay
periods run from the 1st of the month to 15th and 16th to
end of the month. I have all working except for being
able to display Feb 29th, 2004. Following is the formula
that I have entered. Basically, it displays all months
and dates correctly except the PC's internal date must be
on Feb 29th for it to display the 29th. Our time
reporting is done before the 29th, so it should display
the 29th if the month is Feb, year is 2004, and the
reporting date is anytime between the 16th and 29th. Any
help appreciated. Spreadsheet available upon request.

IF(AND(MONTH(H2)=2,(DAY(TODAY())=29)),G36+13,IF(MONTH(H2)
=2,"",IF(DAY(H2)<16,F36,G36)+13))

TIA,
Roger

 

[J.E. McGimpsey]

I'm not sure I understand exactly what you're doing, but if H2
contains a date, this will return the date at the end of the pay
period that contains H2:

=DATE(YEAR(H2),MONTH(H2),IF(DAY(H2)<16, 15,
DAY(DATE(YEAR(H2),MONTH(H2)+1,0))))

To XL, the zeroth day of any month is the last day of the previous
month.

 

[Charles Maxson]

Roger,

Not totally clear on your objectives exactly but to get the last day of the
month for any month I use this formula:

=DATE(YEAR(A1),MONTH(A1)+1,0)

If you want to send me the workbook or more details... go ahead.

I am @

charles
-
AT
-
officezealot
-
DOT
-
com