Compare date with todays date

[Ristos85]

Hi,

In column A I have dates, for example 25.03.2011, 09.04.2011, in Column B ja have status for each date - Open or Due, and in Cell C5 I have formula TODAY(). I would like excel to check if the status is correct according to todays date. For example if date < today and status is Open then it should change the status to Due. It should loop this check until there is a empty cell in column A.

I would be interested of VBA solution.


best regards,
Risto

 

[Trevor G]

Welcome to the forum Risto,

Take a look at this sample

First sheet showing dates and Open

[B]Sheet1[/B]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Dates</TD><TD>Status</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">23/11/2011</TD><TD>Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">05/04/2011</TD><TD>Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">05/04/2011</TD><TD>Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Open</TD></TR></TBODY></TABLE>

[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.mrexcel.com/forum/<a href="]http://www.excel-jeanie-html.de/index.php?f=1[/URL]" target="_blank"> Excel Jeanie HTML 4

Next After running code

[B]Sheet1[/B]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Dates</TD><TD>Status</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">23/11/2011</TD><TD>Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">05/04/2011</TD><TD>Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">01/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">05/04/2011</TD><TD>Open</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">04/04/2011</TD><TD style="BACKGROUND-COLOR: #ffff00">Due</TD></TR></TBODY></TABLE>

[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.mrexcel.com/forum/<a href="]http://www.excel-jeanie-html.de/index.php?f=1[/URL]" target="_blank"> Excel Jeanie HTML 4

And the code I have used. Change the sheet name

Sub checkStatus()
Sheets("[COLOR=red]Sheet1[/COLOR]").Select
Range("a2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value < Date And ActiveCell.Offset(0, 1).Value = "Open" Then
ActiveCell.Offset(0, 1) = "Due"
End If
ActiveCell.Offset(1, 0).Select
Loop
 
End Sub

 

[sudeep]

You can also consider using if.
=if(datadate>todaydate,"Open","Due")
For better identification, you can have conditional formatting done on the cells.

 

[Ristos85]

Thanks for the welcome!

Trevor your solution works perfectly, thanks for quick help!
Sudeep for this case I needed vba solution, but thanks for pointing out another possibilty

 

[Trevor G]

Pleased to read you have a working solution.