Using Current Date Function THANKS!!!

amwilber

Board Regular
Joined
May 15, 2008
Messages
162
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"

THANKS IN ADVANCE!


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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Try this:
Code:
=if((today()-date)>10, what if true, what if false)

Bye.
 
Upvote 0
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...
 
Upvote 0
to find the day difference you can use this formula:
<html><head><title>Excel Jeanie HTML</title></head><body>

<!-- ######### 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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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
Back
Top