Auto calculating the month end

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to work on a template for a bi-weekly employee time sheet. I need
the dates to range from the 1st to the 15th, and then from the 16th till the
end of the month. Is there a formula that can be used so the system would
recognize the months that have 30 or 31 days?
 
My preference is to use the first of the next month minus one. Something like
this...

=DATE(2007, 10, 1) - 1
Placed in a cell gives you Sept 30th...
 
Ok, I tried looking for the eomonth function. Is this an add-on? I have
Office 2003. Would I need 2007 for this function?
 
Or even
=date(2007,10,0)

The zeroeth day of the next month is the last day of the previous month.
 
go to tools/addins and check analysis toolpak and install it if it isn't already
installed. you will probably need the o2k3 cd.
with a date in a1
enter in b1
=eomonth(a1,0)
will give you the last day of the month in cell a1
 
I tried to explain that to someone in the office a while back and they just
didn't quite click in to it. I changed my explanation to the first minus 1
and the light bulb came on. Personally I use the 0 thing but I have never
tried to explain it since. I guess the 0th day of the month was a bit too
conceptual...
 
Can I set it up as an IF function? Like if the date<=15 then it uses the
15th, IFdate>=16 then =eomonth(a1,0) ?

What I want to do is set it up so it will input the end of a pay period as
either the 15th or the end of the month. depending on if it is the first or
second pay period.

Can I use the IF function for that and if so, what would the formula need to
look like?
 
Back
Top