# Using Current Date Function THANKS!!!

#### amwilber

##### Board Regular
Hello all!
I am attempting to use some sort of code that will take in account the current date to tell whether a certain row within my excel spreadsheet it older than 10 days. I am thinking it can be done with a simple "if" statement, but I do not remember how to incorporate the "today" part of it.

As you can see in the example below, I have multiple tickets that I need to track. I want to have some sort of code that will take these different rows and analyze if the Recieved Date (column M) is older than 10 days. If it is older than 10 days I would like it to copy and paste the entire row associated with that ticket into "sheet 2".

As well, I would also like to possibly take a look at the "status" (column L). If the row shows that the status is "sndl2", i would like it to copy and paste that row into "sheet 3"

EDIT: Deleted HTML image - Please use mock data only...No real names or data. Also note that your baby blue text is really hard to read. Moderator
<script language="JavaScript" src="%3Ca%20href=" popup.js"="" colo="" puremis="" sun="">www.interq.or.jp http:></script><center><table" target="_blank"></table">
THANKS SOOOOOO MUCH!!!!
</center>

Last edited by a moderator:

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Anthony47

##### Well-known Member
Try this:
Code:
``=if((today()-date)>10, what if true, what if false)``

Bye.

#### Jonmo1

##### MrExcel MVP
I assume you're basing it on Column M(recieved Date)

Perhaps something like this...
=IF(M2 < = TODAY()-10,"10 Days Old","Not 10 Days Old")
Hope that helps...

#### iggydarsa

##### Well-known Member
to find the day difference you can use this formula:

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
AB
1DateDays Passed
26/20/200810
Sheet3

<!-- ######### End Created Html Code To Copy ########## -->

</body></html>
(dont forget to format numbering to GENERAL)

if you want to copy/paste based on a condition you might need a VBA

#### amwilber

##### Board Regular
Try this:
Code:
``=if((today()-date)>10, what if true, what if false)``

Bye.

This kind of sounds what I am looking for, but how would I get it to copy and paste those particular rows into a different sheet?
Thanks

#### Anthony47

##### Well-known Member
You will need something like this:
Code:
``````For Each Cell in Range("M1:M1000")  'include all the lines of data
If (Date-Cell.value)>10 then
Cell.enireRow.copy destination:=Sheets("Sheets2").Range("A65536").End(xlUp).Offset(1, 0)
Next Cell``````

Bye.

#### amwilber

##### Board Regular
You will need something like this:
Code:
``````For Each Cell in Range("M1:M1000")  'include all the lines of data
If (Date-Cell.value)>10 then
Cell.enireRow.copy destination:=Sheets("Sheets2").Range("A65536").End(xlUp).Offset(1, 0)
Next Cell``````

Bye.

Thank you Anthony! Where do I put this code though?

#### iggydarsa

##### Well-known Member
when excel is on click on ALT+F11, on the new window select INSERT then MODULE from the menu and paste this code (assuming that the code works):

Code:
``````Sub CopyPaste ()
For Each Cell in Range("M1:M1000")  'include all the lines of data
If (Date-Cell.value)>10 then
Cell.enireRow.copy destination:=Sheets("Sheets2").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub``````

then whenever you are ready to run the code, on the spreadsheet menu select TOOLS>MACRO>MACROS, and select CopyPaste from the list and RUN

#### amwilber

##### Board Regular
when excel is on click on ALT+F11, on the new window select INSERT then MODULE from the menu and paste this code (assuming that the code works):

Code:
``````Sub CopyPaste ()
For Each Cell in Range("M1:M1000")  'include all the lines of data
If (Date-Cell.value)>10 then
Cell.enireRow.copy destination:=Sheets("Sheets2").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub``````

then whenever you are ready to run the code, on the spreadsheet menu select TOOLS>MACRO>MACROS, and select CopyPaste from the list and RUN

I guess why I was having trouble is becuase I keep erroring out with this code...I have used macro's before, but i cannot get this thing to work. It errors on the second line of code saying "Runtime error" and highlights "If (Date - Cell.value) > 10 Then"
Any ideas?

#### Anthony47

##### Well-known Member
From excel, press Alt-F11; this will open the vba editor; Menu /Insert /Module; then copy the following code and paste it into the right frame; Modify the "Destination=Sheets("Sheet2") to reflect your real destination sheet for the record to copy; return to excel and run the macro

Code:
``````Sub Macro1()
For Each Cell In Range("M1", Cells(Rows.Count, 13).End(xlUp))
If (Date-Cell.value)>10 then
Cell.enireRow.copy destination:=Sheets("Sheets2").Cells(Rows.count,1).End(xlUp).Offset(1, 0)
Next Cell
End Sub``````

Bye.

Replies
3
Views
930
Replies
1
Views
877
Replies
0
Views
482
Replies
1
Views
613
Replies
0
Views
359

1,191,056
Messages
5,984,395
Members
439,884
Latest member
BrownEyedGirl

### 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?

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