Interesting date puzzle

[smcrocker]

I'm going to post this without a workbook for now. I have a table that is pulled in through powerpivot it's pretty much a simple table with about 26 records that get put into the table in SQL then brought over in powerpivot daily. What I need to figure out how to do, if find the date minus one day which sounds very simple on the surface but the spreadsheet is based on monthly data not all of the data, so how if i'm pulling the current day of the month say May 1 go back to the last date of the last month.


Data information:

I have all of the dates for the last 2 months (every day) however the previous months/years has gaps in the data as far as dates for example: if I have say April that the last day of the month is saturday or sunday the data is not there because it was a manual entry (not it's automatic in the database).


My Question:

How can I go back to the last entry date for the month when looking at the first day of the month Ex Need the following 2 days example : may31 (Previous day) June 1 (Current day).


Any idea's on where to look. Trying to automate a manual process that should be easy.

 

[NARAYANK991]

Hi ,


The function EOMONTH(date,number of months) returns the last date in the specified month.


For example =EOMONTH(DATEVALUE("11/22/2011"),0) will return the last date in November 2011 , which is 11/30/2011.


Now , if you wish to look up this last date of the month , for any earlier data , you can use the MATCH function ; the third parameter in this function is used to specify whether you are looking for an exact match , or for a value which is less than the lookup value , or for a value which is greater than the lookup value.


Narayan