Solved Excel Problem If statement

November 16, 2013 at 03:44:25
Specs: Windows 7
How can i put the same dates upto 73 row and after that a blank second date will come ?

I have 60000 rows in one sheet, and I have to put the same dates for 73 rows and a blank is there after every 73 rows.

My starting date is 1/1/2012 and it continues to 26/10/2013. So I have to put 1/1/2012 in 73 rows and after a blank in next 73 rows the values will come 2/1/2013 and so on ..?

See More: Excel Problem If statement

Report •

November 16, 2013 at 17:29:25
The easiest way would probably be to use VBA,
but sorry to say, my VBA skills are just above nil.

But, you could try this and see how it works for you.

In the first 73 cells enter your first date, 01/01/2013

Next in cell 75, because 74 is blank, enter the formula: =IF(A1<>"",A1+1,"")

Next drag down 6000 cells and you should get what your looking for.

Don't forget to do a Copy / Paste / Values after your done dragging down,
because you only have formulas in the cells, not real values.

See how that works.


Report •

November 17, 2013 at 06:51:29
✔ Best Answer
Something doesn't make sense.

Your first date is 1/1/2012 (January 1, 2012)
The next date is 2/1/2013 (January 2, 2013)
Your last date is 26/10/2013 (October 26, 2013)

I don't see how that progression would work.

Is there a typo in your post? Should the second date be 2/1/2012 (January 2, 2012)

If you are asking to increase the date from 1/1/2012 to 26/10/2013 by 1 day every 73 rows with a blank cell between each date, it's only going to take 49,210 rows, not 60,000.

In any case, this code will produce what I think you want.

Sub Lots_Of_Dates()
'Place the word Date in A1
  Cells(1, 1) = "Dates"
'Place the first Date in A2
  myDates = DateValue("1/1/2012")
'Loop through Column A
   For allDates = 2 To 49210 Step 73
'Put Dates in ranges
     Range(Cells(allDates, 1), Cells(allDates + 72, 1)) = myDates
'Create a Blank cell after each 73 Dates
      Cells(allDates + 73, 1) = ""
'Increment counter to account for Blank cell
        allDates = allDates + 1
'Increment Date
        myDates = myDates + 1
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

November 19, 2013 at 08:44:43
Thanks for the would be great if you can help me in one more question.

I have 5 readings in excel. Can we extend this average upto 3 months data ?

I have some readings for 2 days ( can say 5-6 readings). I already take sum, min , max and average of these readings.

So, I want to extend these values to 3 months. Please reply if you will able to solve this.

Report •

Related Solutions

November 19, 2013 at 15:56:29

A few comments on your latest post...

1 - You thanked us for our replies, but you didn't tell us if our suggestions worked for you. Since we put a little effort into trying to solve your issue, it would be nice to know if we actually helped or not. It also helps anyone searching this forum to know that a problem has been solved.

2 - Your latest question does not appear to be related to your original question, so it should be posted in a new thread, with a relevant subject line.

3 - When you post your question in a new thread, please try to be a little more specific in what you are trying to do. Your requirements are not very clear. It might help if you included some example data. Before posting example data in your question, please click on the following link and read the instructions on how to post example data in this forum. Don't forget to include Row and Column labels.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

November 27, 2013 at 08:55:26
Yes..this worked for me. Thanks for your comments. It helped a lot

Report •

Ask Question