The question was:

Need to create series with dates in intervals of 6 weeks 24 weeks and 52 weeks. want it to be set to do it automatically. Can anyone help me?

The answer is simple.  You can use the Excel Date functions to do this:

To break down what is happening here, using “24 weeks from today”, as an example.

The formula is
=DATE(YEAR(B2),MONTH(B2), DAY(B2)+168)
=DATE(2009,MONTH(B2), DAY(B2)+168)
=DATE(2009,12, DAY(B2)+168)
=DATE(2009,12,29+168) <- (168 days equals 7 days time 24 weeks)
=15 June 2010

Update:
Another way to do this is by using the TODAY verb.
ie.  =TODAY()+168
(with thanks to The Angry Technician)

exclaimed a colleague.

“Is that like Visual Basic?”

Well kind of, VBA is Visual Basic for Applications.  Which is a scripting language for the Microsoft Office system.

I’d had a problem where an extract of an asset report only had the “Last user” and “Last software scan date” on the first record for each computer listed.
I needed each line to have entries, as I was going to sort the data later.

but I wanted this:

There was over 4000 records in the extract, so I couldn’t “Fill Down”.

I spent some time looking at Excel spreadsheet functions, particularly the IF function, and kept on running into issues.

So I wrote a VBA Macro.

Dim RowNumber As Integer
Dim ComputerNameColumn As Integer
Dim LastUserColumn As Integer
Dim LastSoftwareScanDateColumn As Integer

ComputerNameColumn = 1
LastUserNameColumn = 2
LastSoftwareScanDateColumn = 4

For RowNumber = 2 To 2324
‘ compare Computer Names, if same the current computer name is same as the last
‘ copy the previous “Last User, Last Software Scan Date” columns.
If Cells(RowNumber, ComputerNameColumn) = Cells(RowNumber – 1, ComputerNameColumn) Then
Cells(RowNumber, LastUserNameColumn) = Cells(RowNumber – 1, LastUserNameColumn)
Cells(RowNumber, LastSoftwareScanDateColumn) = Cells(RowNumber – 1, LastSoftwareScanDateColumn)
‘
End If
Next

Yes, it is simple, and you can see it was written for one time use^*.  But it worked.

You can download the sample spreadsheet here.

^* – for persistent use, you might want to detect things like the ActiveSheet, the number of rows; and whether the “Last user/Last software scan date” cells are actually blank before you overwrite them.

I’m doing some analysis of Help Desk data, and I often use Excel for this task.  Now I know a helpdesk caller did log a problem with the subject “five things I hate about my iPhone”.

But Excel couldn’t find it (see picture right).

The problem was caused by the Excel feature “Find by format” feature (red dot).  To turn it off, click on the Format button (green dot), and then select the Clear option.

VLookup allows you to check to see if cell A exists in a range of cells.  This might be useful if you want to compare two lists of names, or DVD’s for example.

Just like the example pictured here:

(click on the picture to see all of it)

The formula used above is: =VLOOKUP(A6,DVDsIown!A:A,1,FALSE)

It’s simple once you get the hang of it.  You can download the Excel spreadsheet I used above, here.

References:
Microsoft Excel Online Help: VLookup reference
Tech on the net: Excel VLookup Function