# vlookup - day (-) 1

#### omypet

##### Board Regular
Still looking for best formula here!

My worksheet date is 01 Nov 2006, day(1)-1 is equal 0, since oct (jan, mar, may, jul, aug & dec) has 31 days.

For 01 Oct 2006 worksheet this formula works.

IF(DAY(\$H\$1)=1,VLOOKUP(DAY(\$H\$1)+29,\$A\$11:\$N\$44,7,0),VLOOKUP(DAY(\$H\$1)-1,\$A\$11:\$N\$44,7,0))

The date of worksheet is behind by 1 day of the report so 01 Oct 2006 report is actually the whole whole date of Sept 1 to 30

Tks..

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Andrew Fergus

##### MrExcel MVP
Hi

What are you trying to do? It appears you are trying to get the last day of the previous month - is that correct? If so, have you considered using the EOMonth() function? It is available as a formula if you install the Analysis ToolPak. To do this click menu option Tools > Add-Ins > tick 'Analysis ToolPak' > OK. Then check out the syntax for EOMonth in your help file.

HTH, Andrew

#### omypet

##### Board Regular
Hello Andrew,

That's right, I am trying to get the last day of the previous month to get those data into 1st of the suceeding month. For months ending 30 it works,
another is for February, can't get the standard formula on this.

Ok thanks for the info, I will try to sort the syntax of the eomonth() function.

Cheers,

#### Peter_SSs

##### MrExcel MVP, Moderator
omypet

It is still not clear to me what you are trying to do. However, this may be some use. For the date in H1, the date of the last day of the previous month is given by:
=\$H\$1-DAY(\$H\$1)
and if you want the day number of this (28, 30, 31) then use
=DAY(\$H\$1-DAY(\$H\$1))

Have you tried Colo's HTML Maker for showing sheet samples on the board?
http://www.mrexcel.com/board2/viewtopic.php?t=92622

#### omypet

##### Board Regular
Peter

Sorry for misleading, I got an idea now thanks, I can play now with this suggestions.

cheers

Replies
5
Views
74
Replies
10
Views
130
Replies
3
Views
76
Replies
10
Views
156
Replies
1
Views
117