vlookup - day (-) 1

omypet

Board Regular
Joined
Oct 8, 2005
Messages
71
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..
 

Some videos you may like

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
Joined
Sep 9, 2004
Messages
5,432
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
Joined
Oct 8, 2005
Messages
71
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
Joined
May 28, 2005
Messages
46,871
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 8, 2005
Messages
71
Peter

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

cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,114,088
Messages
5,545,889
Members
410,711
Latest member
Josh324
Top