Lookup of dates

americanpie3

Board Regular
Joined
Jul 16, 2003
Messages
180
Here's my worksheet (sheet 1):
SoftwareDB.xls
ABCDEFG
1SoftwareandLicenseDB
2
3CompanySoftwareSoldSupplierLicensessoldDatesoldEffectivelicensedateEnddate
4
5TrafficTechBackupExec11D422/02/200722/02/200721/02/2008
6TrafficTechBackupExec11D122/02/200722/02/200721/02/2007
7SogelcoFaxTalkPro7.5110/9/200710/9/2007
8CanlyteWebsense10/3/200510/3/200710/3/2008
9CanlyteInternicdomainname
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Well-known Member
Joined
Apr 8, 2002
Messages
912
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

Board Regular
Joined
Jul 16, 2003
Messages
180
I've implemented your formula but everything comes up false:
SoftwareDB.xls
ABCDEFGHI
1SoftwareandLicenseDB
2
3CompanySoftwareSoldSupplierLicensessoldDatesoldEffectivelicensedateEnddatePrice
4
5TrafficTechBackupExec11D422/02/200722/02/200721/02/2008FALSE
6TrafficTechBackupExec11D122/02/200722/02/200721/02/2008FALSE
7SogelcoFaxTalkPro7.5110/9/200710/9/200720/09/2007FALSE
8CanlyteWebsense10/3/200510/3/200710/3/2008FALSE
Sheet1
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top