Lookup of dates

[americanpie3]

Here's my worksheet (sheet 1):

SoftwareDB.xls
	A	B	C	D	E	F	G
1	SoftwareandLicenseDB
2
3	Company	SoftwareSold	Supplier	Licensessold	Datesold	Effectivelicensedate	Enddate
4
5	TrafficTech	BackupExec11D		4	22/02/2007	22/02/2007	21/02/2008
6	TrafficTech	BackupExec11D		1	22/02/2007	22/02/2007	21/02/2007
7	Sogelco	FaxTalkPro7.5		1	10/9/2007	10/9/2007
8	Canlyte	Websense			10/3/2005	10/3/2007	10/3/2008
9	Canlyte	Internicdomainname
Sheet1

What I would like is to create a lookup of column G (end date) which is 6 weeks away from today's date. Or if it will help to better create the formula 45 days days away from reaching hte end date.

I would create the formula in sheet 2.

Thanx

 

[venkat1926]

are the dates entred like this or formattead like this. If they have been enterd like this they are not dates but texts.and you cannot caluclate text +45.

in excel you enter as
mm/dd/yy ie.g 2/21/08
you can format as you like as default formats or even custome format.
if necessary redesign the worksheet.

if in any cell if you type
=today()+45
will give 45 days after today. But today() will change everyday you open the file.

 

[inarbeth]

A different approach might be to use Conditional Formatting to highlight any date in G that was within 45 days of Today's date. Select the range starting at G5 then select Format. Conditional Formatting and then Formula and use this formula =AND(G5>TODAY(),TODAY()+45>G5) and select your formatting. The double test eliminates blanks and dates in G which have passed. If you don't need that then =TODAY()+45>G5 will also work

 

[americanpie3]

I've implemented your formula but everything comes up false:

SoftwareDB.xls
	A	B	C	D	E	F	G	H	I
1	SoftwareandLicenseDB
2
3	Company	SoftwareSold	Supplier	Licensessold	Datesold	Effectivelicensedate	Enddate		Price
4
5	TrafficTech	BackupExec11D		4	22/02/2007	22/02/2007	21/02/2008	FALSE
6	TrafficTech	BackupExec11D		1	22/02/2007	22/02/2007	21/02/2008	FALSE
7	Sogelco	FaxTalkPro7.5		1	10/9/2007	10/9/2007	20/09/2007	FALSE
8	Canlyte	Websense			10/3/2005	10/3/2007	10/3/2008	FALSE
Sheet1

 

[xenou]

You'd better play around with the date fields and make sure you have true dates. Use Edit | Clear | Formats to clear all the formatting...if these are true dates, you will get numbers like 39345. If not, clear all the formatting then re-enter your dates.

It seems to me that once you have true dates and use the formula given, you could just filter on the trues/falses or use conditional formatting to highlight the rows you are interested in ... not sure why you need another sheet...But I am probably missing something.

Regards.