Date

[Phil]

In column A i have dates listed every 7 days. Is it possible using a formula
to go to the last date showing for every month and then enter a value in
column E for that date. The last listed date for each month is variable ,
for example..

20/07/04
27/07/04
03/08/04
10/08/04
17/08/04
24/08/04
31/08/04
07/09/04

Here i would like 27/07/04 , 31/08/04

Thanks

[Jack Sheet]

One possibility

Something like:

In cell E10 enter formula
=IF(MONTH(A11)>MONTH(A10),Expression,"")
copy the formula down to each row for which there is a corresponding entry
in column A
You may need to amend the formula to cater for the last row.
You could also have it return zero instead of "" when the condition fails,
and custom number format the cell so that zero values are not displayed.
You might want to use MONTH(OFFSET(A10,1,0)) instead of MONTH(A11) if you
are in the habit of inserting rows in the middle of the data table.


"Phil" <[email protected]> wrote in message
news:[email protected]...
> In column A i have dates listed every 7 days. Is it possible using a
formula
> to go to the last date showing for every month and then enter a value in
> column E for that date. The last listed date for each month is variable ,
> for example..
>
> 20/07/04
> 27/07/04
> 03/08/04
> 10/08/04
> 17/08/04
> 24/08/04
> 31/08/04
> 07/09/04
>
> Here i would like 27/07/04 , 31/08/04
>
> Thanks
>
>

[Aladin Akyurek]

Your problem is underspecified. It's for example unclear which
month/year to look for.

Phil wrote:
> In column A i have dates listed every 7 days. Is it possible using a formula
> to go to the last date showing for every month and then enter a value in
> column E for that date. The last listed date for each month is variable ,
> for example..
>
> 20/07/04
> 27/07/04
> 03/08/04
> 10/08/04
> 17/08/04
> 24/08/04
> 31/08/04
> 07/09/04
>
> Here i would like 27/07/04 , 31/08/04
>
> Thanks
>
>

[Don Guillett]

This assumes you have the date for the last day of the month desired entered
in cell b1
Sub FindlastDate()
x = Application.Match([b1], Columns(1), 1)
Cells(x, "e") = 111
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Phil" <[email protected]> wrote in message
news:[email protected]...
> In column A i have dates listed every 7 days. Is it possible using a
formula
> to go to the last date showing for every month and then enter a value in
> column E for that date. The last listed date for each month is variable ,
> for example..
>
> 20/07/04
> 27/07/04
> 03/08/04
> 10/08/04
> 17/08/04
> 24/08/04
> 31/08/04
> 07/09/04
>
> Here i would like 27/07/04 , 31/08/04
>
> Thanks
>
>

[Phil]

Thanks, with a little adapting your formula worked fine.

"Jack Sheet" <[email protected]> wrote in message
news:%23Q%[email protected]...
> One possibility
>
> Something like:
>
> In cell E10 enter formula
> =IF(MONTH(A11)>MONTH(A10),Expression,"")
> copy the formula down to each row for which there is a corresponding entry
> in column A
> You may need to amend the formula to cater for the last row.
> You could also have it return zero instead of "" when the condition fails,
> and custom number format the cell so that zero values are not displayed.
> You might want to use MONTH(OFFSET(A10,1,0)) instead of MONTH(A11) if you
> are in the habit of inserting rows in the middle of the data table.
>
>
> "Phil" <[email protected]> wrote in message
> news:[email protected]...
> > In column A i have dates listed every 7 days. Is it possible using a
> formula
> > to go to the last date showing for every month and then enter a value in
> > column E for that date. The last listed date for each month is variable
,
> > for example..
> >
> > 20/07/04
> > 27/07/04
> > 03/08/04
> > 10/08/04
> > 17/08/04
> > 24/08/04
> > 31/08/04
> > 07/09/04
> >
> > Here i would like 27/07/04 , 31/08/04
> >
> > Thanks
> >
> >
>
>