Date function

[Jim Peterson]

Hi everyone and thanks for any help you can give me. One of you guys always
comes upwith the answer I need.

Anyhow, I am running XP Pro and Excel 2003. I have a workbook with twelve
individual pages (one for each month). Each page has lines available for data
entry for each day of the month. In cell B4 I manually enter the current year
(i.e. 2008).

On the first page (Jan) I want cell A6 to return 01/01/08 (I have the cell
formatted for xx/xx/xx), cell A7 to return 01/02/08, etc. What formula would
I use to accomplish this?

I also need to do the same thing on each month's page, so how would the
formula be different on the Feb page?

Finally, on each page cell A45 is to always return the last day of that
particular month.

I'm hopng someone is a pretty good whiz at this and can fire back some help.

Thanks again,
Jim

 

[Sandy Mann]

For the start date in A6 use:

=DATE(B4,1,1)

In A7 use:

=A6+1

If you want the formula to automatically stop after the end of the month
then use:

=IF(MONTH(A6+1)=MONTH(A6),A6+1,"")

But that will really only be needed in Row 34 (29th of the month) wher it
would be:

=IF(MONTH(A33+1)=MONTH(A33),A33+1,"")

For the last day of the month in A45 you could use:

=MAX(A6:A36)

This would then work for all months.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

In B4 enter 1/1/2008 for January 1, 2008......2008 alone is not a valid date.

In A6 enter =B4

In A7 enter =A6+1

Copy down to A36

In A45 enter =A36

Must be done to all 12 worksheets(not "pages") with date in B4 changing for each
month.


Gord Dibben MS Excel MVP

n Sun, 1 Jun 2008 14:14:01 -0700, Jim Peterson

 

[Gord Dibben]

Much gooder with error traps and stuff<g>

Gord

 

[Jim Peterson]

Thanks for your help Sandy!
Everything you suggested worked perfectly.
Thanks again,
Jim

 

[Sandy Mann]

You are very welcome. Tanks for taking the time to post back.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk