Using Current Date Function THANKS!!!

A

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

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.
Sort by date Sort by votes
A

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,441
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this:
Code: 
=if((today()-date)>10, what if true, what if false)

Bye.
 
Upvote 0
Jonmo1

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
iggydarsa

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,734
Office Version
  1. 2019
Platform
  1. Windows
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
A

amwilber

Board Regular
Joined
May 15, 2008
Messages
162
Anthony47 said:
Try this:
Code: 
=if((today()-date)>10, what if true, what if false)

Bye.
Click to expand...


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 :)
 
Upvote 0
A

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,441
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
A

amwilber

Board Regular
Joined
May 15, 2008
Messages
162
Anthony47 said:
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.
Click to expand...



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

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,734
Office Version
  1. 2019
Platform
  1. Windows
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
A

amwilber

Board Regular
Joined
May 15, 2008
Messages
162
iggydarsa said:
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
Click to expand...



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
A

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,441
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
You must log in or register to reply here.

Similar threads

E
  • Solved
stop conditional formatting when date has passed even if condition is met
Replies
3
Views
930
excelnewby22
E
M
  • Locked
  • Question
Use VBA to select a range based on the first cell of todays date in Axx and the last row column Fxx?
Replies
1
Views
877
Fluff
Fluff
M
  • Question
Help with FilterXML formula
Replies
0
Views
482
montex01
M
D
  • Question
Return a value from a list based on position in date range
Replies
1
Views
613
Brunts
B
M
  • Question
Sorting Pivot Table by date in Macro
Replies
0
Views
359
michaelfrye
M

Forum statistics

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

Share this page

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