Excel fill series conundrum

  cedricthecat 11:10 22 Jan 2007
Locked

Trying to compile an ongoing spreadsheet, which needs three rows for each weekday.

If I do "fill series" on the date, I get, for example:-

22/1/2007
23/1/2007
24/1/2007
25/1/2007
26/1/2007

When I want

22/1/2007
22/1/2007
22/1/2007
23/1/2007
23/1/2007
23/1/2007 etc

Is there any easy way to do this with a series fill, or will it have to be either a manual job or a macro?

Thanks in advance!

  VoG II 11:34 22 Jan 2007

There's probably a clever formula to do this but here's a macro:


Sub DateSeries()
Dim StartDate As Date
Dim i, j, k
StartDate = "21/1/2007"
k = 1
For i = 1 To 90 ' change 90 to suit
For j = 1 To 3
Cells(k, 1).Value = StartDate + i
k = k + 1
Next j
Next i
End Sub

To use this, press ALT + F11 to open the Visual Basic Editor. Insert > Module. Then copy the code and paste it in. Close the VBE. Tools > Macro > Macros, click on DateSeries and click Run.

Note: this will over-write whatever is in column A.

  Simsy 11:45 22 Jan 2007

worked out a way... but I don't know if this will be suitable...

Have the first date in A1

In A2 have the formula =A1

In A3 have the formula =A2


Select all 3 cells and drag down, and it seems to do what you want.

If having the formulae is a problem, when you've done it, select the whole range, copy it, then right select, choose "Paste special" and then choose "Values" from the list.

Hope this helps,

Regards,

Simsy

  cedricthecat 12:13 22 Jan 2007

Thanks Vog (not tried the macro, but I know it'll work, as you've helped me out with macros before - when I've posted as Monty Burns, which I would do now, but I'm at work and forgotten the logon!)

Thanks also Simsy - this worked a treat!

Just to add some interest, I also need to add single rows for Saturday and Sunday - to visually break the weeks up. Could just add empty rows I guess, but I like the challenge! Is it possible?!

  cedricthecat 12:22 22 Jan 2007

Yes it is! And I worked it out myself! :-)

Works like this:-

A1 is the first Monday date
A2=A1
A3=A1

Then dragged them down and entered

A16=A15+1
A17=A16+1
A18=A17+1
A19=A18
A20=A19

Then dragged them all down for another week.

Finally, to keep repeating the series, I block copied and pasted.

Well chuffed! Cheers!

  VoG II 12:30 22 Jan 2007

Or a macro!

Sub DateSeries()
Dim StartDate As Date, ThisDate As Date
Dim i, j, k
StartDate = "21/1/2007"
k = 1
For i = 1 To 90 ' change 90 to suit
ThisDate = StartDate + i
If Weekday(ThisDate) = 1 Or Weekday(ThisDate) = 7 Then
Cells(k, 1).Value = ThisDate
k = k + 1
Else
For j = 1 To 3
Cells(k, 1).Value = ThisDate
k = k + 1
Next j
End If
Next i
End Sub

  cedricthecat 12:41 22 Jan 2007

Or indeed, a macro!

;-)

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

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Microsoft Surface Book 2 hands-on review – bigger and 5x faster

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?