Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 "Phil" wrote in message ... 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 |
#5
|
|||
|
|||
Thanks, with a little adapting your formula worked fine.
"Jack Sheet" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |