Date function
 

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

Date function
 

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


Replace @mailinator.com with @tiscali.co.uk


"Jim Peterson" wrote in message
...
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

Date function
 

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
wrote:

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

Date function
 

Much gooder with error traps and stuff<g

Gord

On Sun, 1 Jun 2008 22:56:45 +0100, "Sandy Mann"
wrote:

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.

Date function
 

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

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Jim Peterson" wrote in message
...
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

Date function
 

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


Replace @mailinator.com with @tiscali.co.uk


"Jim Peterson" wrote in message
...
Thanks for your help Sandy!
Everything you suggested worked perfectly.
Thanks again,
Jim

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Jim Peterson" wrote in message
...
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