Excel: how do I calculate the date 24 weeks from today?

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:

Excel Date Functions

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)

Bookmark and Share

“VBA? You wrote VBA code for that?”

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.

Excel screenshot showing blank cells. but I wanted this: Excel screenshot showing cells filled in CopyCell macro

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.

Bookmark and Share

Excel – Merging columns using the Concatenate function

Excel Concatenate function

Bookmark and Share

Microsoft Office Excel cannot find the data you’re searching for.

Excel find and replace not working.

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.

Bookmark and Share

How to use VLOOKUP on Microsoft Excel

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:

vlookup_example

(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

Calendar

March 2010
S M T W T F S
« Feb    
 123456
78910111213
14151617181920
21222324252627
28293031