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?

if you install the analysis toolpak, you can use the eomonth function.

--


Gary


"Rip1877" <(E-Mail Removed)> wrote in message
news:7996B493-7543-4242-95C4-(E-Mail Removed)...
>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?

Rip1877,

Try the function =Date(year,month,day)


"Rip1877" wrote:

> 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...
--
HTH...

Jim Thomlinson


"Rip1877" wrote:

> 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?

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.



Jim Thomlinson wrote:
>
> 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...
> --
> HTH...
>
> Jim Thomlinson
>
> "Rip1877" wrote:
>
> > 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?

--

Dave Peterson

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

--


Gary


"Rip1877" <(E-Mail Removed)> wrote in message
news:1914DAAF-D102-41F0-A569-(E-Mail Removed)...
> Ok, I tried looking for the eomonth function. Is this an add-on? I have
> Office 2003. Would I need 2007 for this function?

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...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

> Or even
> =date(2007,10,0)
>
> The zeroeth day of the next month is the last day of the previous month.
>
>
>
> Jim Thomlinson wrote:
> >
> > 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...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> > "Rip1877" wrote:
> >
> > > 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?
>
> --
>
> Dave Peterson
>

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?

"Gary Keramidas" wrote:

> 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
>
> --
>
>
> Gary