A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

If Date In Cell Is Greater Than Todays



 
 
Thread Tools Display Modes
  #1  
Old October 5th 10, 02:33 PM
CRAIGJACK CRAIGJACK is offline
Junior Member
 
First recorded activity by ExcelBanter: Oct 2010
Posts: 2
Default If Date In Cell Is Greater Than Todays

hello i wonder if you could help?

i have a date in Cell E3 and i would like it to be highlighted yellow if todays date is 30 days near to the date in E3?
thanks
Ads
  #2  
Old October 5th 10, 03:10 PM
Mazzaropi Mazzaropi is offline
Senior Member
 
First recorded activity by ExcelBanter: Jul 2010
Location: Belo Horizonte, Brazil
Posts: 169
Thumbs up

Quote:
Originally Posted by CRAIGJACK View Post
hello i wonder if you could help?

i have a date in Cell E3 and i would like it to be highlighted yellow if todays date is 30 days near to the date in E3?
thanks

Dear CRAIGJACK, Good Morning.

As you said, E3 is your date cell.
E3 = 11/05/2010 (mm/dd/aaaa)

Current date (today) is 10/05/2010 (mm/dd/aaaa)

The function =TODAY() shows up the current date.

I suggest you to use Conditional Formatting to follow your desired rules. DO:

The rule there will be implemented:
...... 1) If the cell E3 is Greater or Equal than Current DATE, AND Less than 30 days from now, then Cell Background turns to Yellow (you can choose the color you want)

How to do Conditional Formatting:
...... Click on E3
.......... Menu -> Format -> Conditional Formatting

When the dialog box appears, DO:
Condition 1: First field: The formula is
.................... Second field: type: =AND((E3-TODAY())<=30,(E3-TODAY())>=0)

Button: Format and choose the background color you like(eg. YELLOW).

Once you've finished it : OK

Its working.
Try to put diferent dates on E3 to test the change of background colors

Feel free to do any questions about it.
__________________
I hope it can help you.

Best regards,
Marcilio Lobo
---------------------------
Belo Horizonte, Brazil

Last edited by Mazzaropi : October 5th 10 at 03:15 PM.
  #3  
Old October 5th 10, 03:32 PM
CRAIGJACK CRAIGJACK is offline
Junior Member
 
First recorded activity by ExcelBanter: Oct 2010
Posts: 2
Default

Quote:
Originally Posted by Mazzaropi View Post
Dear CRAIGJACK, Good Morning.

As you said, E3 is your date cell.
E3 = 11/05/2010 (mm/dd/aaaa)

Current date (today) is 10/05/2010 (mm/dd/aaaa)

The function =TODAY() shows up the current date.

I suggest you to use Conditional Formatting to follow your desired rules. DO:

The rule there will be implemented:
...... 1) If the cell E3 is Greater or Equal than Current DATE, AND Less than 30 days from now, then Cell Background turns to Yellow (you can choose the color you want)

How to do Conditional Formatting:
...... Click on E3
.......... Menu -> Format -> Conditional Formatting

When the dialog box appears, DO:
Condition 1: First field: The formula is
.................... Second field: type: =AND((E3-TODAY())<=30,(E3-TODAY())>=0)

Button: Format and choose the background color you like(eg. YELLOW).

Once you've finished it : OK

Its working.
Try to put diferent dates on E3 to test the change of background colors

Feel free to do any questions about it.
Ok thank you,
but now what if there are mutiple dates in Column E?
will the formula be:

=AND((E$3$-TODAY())<=30,(E$3$-TODAY())>=0) ?
  #4  
Old June 3rd 14, 07:17 AM
CptnRsk CptnRsk is offline
Junior Member
 
First recorded activity by ExcelBanter: Jun 2014
Posts: 1
Default

Quote:
Originally Posted by CRAIGJACK View Post
Ok thank you,
but now what if there are mutiple dates in Column E?
will the formula be:

=AND((E$3$-TODAY())<=30,(E$3$-TODAY())>=0) ?
Hi, I am new to excel formulas and could use some help with this and my idea/logic....

I was able to get this to work, but I want a third option. Basically like this.

If more than 30 days to due date = Paid
If less than 30 days to due date = Due
If date equal to due date +1 = Overdue..

I have it working with =IF((Status!B2-TODAY())>=30,K1,L1)
K1 = Paid
L1 = Due
J1 = Overdue


I played with it a little bit and came up with =IF((Status!B2-TODAY())>=30,K1,L1)&IF((Status!B2-Today())<>1,J1,K1)

And I tried this =IF((Status!B2-TODAY())>=30,K1,L1)&IF((Status!B2-Today())=+1,J1) But shows DueOverdue

As you can see it shows two entries. It as if it still shows the result of the caluclation based on the original 30 days argument.

Any help to get the third option of J1, to be a viable argument in this formula would be much appreciated.

Thanks
Cptn

Last edited by CptnRsk : June 3rd 14 at 07:26 AM.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofill cell based on todays date Tomahawk Excel Worksheet Functions 3 August 22nd 08 10:43 PM
Create a button that will date stamp todays date in a cell Tom Meacham Excel Discussion (Misc queries) 3 January 11th 06 02:08 AM
Put todays date in a cell by checking a box!! Martin Excel Discussion (Misc queries) 1 October 24th 05 09:01 PM
In Excel, Get todays date in a cell tbw Excel Discussion (Misc queries) 2 October 6th 05 12:08 AM
How do I filter a list using a greater than todays date function? LV Excel Worksheet Functions 2 April 29th 05 06:07 PM


All times are GMT +1. The time now is 02:02 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.