A question on Excel

  PaulOwen 09:07 23 May 04
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 04

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 04

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

  VoG II 09:42 23 May 04

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 04

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 04

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

  PaulOwen 10:06 23 May 04

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

  PaulOwen 10:18 23 May 04

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 04

^

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

  VoG II 14:06 23 May 04

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.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Why ecommerce hasn't taken off on social media

New MacBook Pro 2016 review | MacBook Pro with Touch Bar review: Apple's expensive and powerful…