A question on Excel

  PaulOwen 09:07 23 May 2004
Locked

I am doing a spreadsheet. What I want to know is, is there a formula that I can put a date in for the last day of each month for each cell rather than typing it in everytime? (I have several to do) I believe you type the formula in one cell then drag the + sign down to enter the formula in each cell after. Many thanks.

Paul

  VoG II 09:29 23 May 2004

You can use EOMONTH which is included in the Analysis ToolPak. The ToolpPak is not installed by default.

Say you have the date 1-JAN-04 in cell A3. Then in B3 enter the formula

=EOMONTH(A3,0)

and format cell B3 as a date. You can then drag the formula down.

To load the Analysis ToolPak, Tools/Add-ins and tick Analysis ToolPak.

  PaulOwen 09:37 23 May 2004

I tried that but it enters the same date in each cell!?

  VoG II 09:42 23 May 2004

I've tried it:

with 1-jan-04 in cell A3 it returns 31-jan-04 in B3

with 1-feb-04 in A4 it returns 29-feb-04 in B4

and so on.

Perhaps a bit more explanation of what data you have and what you are trying to achieve...

  PaulOwen 09:51 23 May 2004

I already have formulas in columns either side. I am trying a similar spreadsheet as to that of Daz's you helped him with yestarday i.e balances, closing balances etc. Also are you able to do it as the last working/weekday of each month rather than the last day?

  VoG II 10:00 23 May 2004

Have a read click here and see if any of it helps.

  PaulOwen 10:06 23 May 2004

Thanks VoG that is well complicated and advanced for me! I think I will type in each date.

  PaulOwen 10:18 23 May 2004

Going back to your original suggestion will it help if I arrange my spreadsheet so I don't have any formulas in the columns either side of the date column?

  PaulOwen 12:02 23 May 2004

^

I have got to the same point as Paul if someone could help further.

  VoG II 14:06 23 May 2004

It shouldn't matter what is in other columns. Are you saying that the above formula does not work when you copy it down the column?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone