[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Date Diff Issue in Excel VB

Posted on 2009-04-14
7
Medium Priority
?
574 Views
Last Modified: 2012-08-13
I think I have dates finally figured out and then the whole date diff thing is throwing me for a loop. I have a column (AW) of dates. What I want to do is determine if the date in the column is greater than 4 years in the past (in this case I'm using days so that if it's greater than 1460 days) then to enter the number 1 44 columns to the right of the date in the cell. My dates are formatted in column AW as MM-DD-YYYY. I expect I'm having a problem with not seting a date specificaion type but unsure really how to do it. I've complicated my code so much that I don't even know what's right anymore and unsure if a case is better to use than a nested if then.
I think if someone can just help me figure out the syntax of
If DateDiff("d" date1,date2 ) > 1460 then
cell.Offset(0, 44) = 1

where date 1 is the date value in a cell in column AW Thanks
0
Comment
Question by:luminent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 17

Expert Comment

by:Alan
ID: 24144686
This will compare B2 and C2 (with C2 being the 'anchor' cell) and put a 1 across 44 cols from C2.

Alan.

 
Sub Test()
 
Set MyCell = Range("C2")
 
    If DateDiff("d", MyCell.Offset(0, -1).Value, MyCell) > 1460 Then
    
        MyCell.Offset(0, 44).Value = 1
 
    End If
 
End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24144699
don't understand by "What I want to do is determine if the date in the column is greater than 4 years in the past"????

you enter first date in AW column than where is second data?

Please elaborate your question bit more.
0
 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
ID: 24144843
Your question is not clear :
if you want to compare a cell having date value with particular date and want to put any spcific value in specific col. then use this :
if(b1-c1>1460,"put your true value","else false value")
put the above command in cell where you want the result.

Regards,

Bm Keshav

0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24145081
If you are concerned at text form dates then forcing them to dates may help:

If DateDiff("d" cdate(date1),cdate(date2)) > 1460 then
cell.Offset(0, 44) = 1

Chris
0
 
LVL 9

Accepted Solution

by:
felixdsouza earned 1000 total points
ID: 24145178
Try out this..
Sub Test()
    Dim DtToday As Date
    Dim Dt As Date
    
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim CurrentRow As Long
    
    Dim CurrentCol As Integer
    FirstRow = 1
    LastRow = [AW65536].End(xlUp).Row
    
    DtToday = Int(Now)
    CurrentCol = [AW1].Column
    For CurrentRow = FirstRow To LastRow
        Cells(CurrentRow, CurrentCol).Select
        Dt = Cells(CurrentRow, CurrentCol)
        If DateDiff("d", Dt, DtToday) > 1460 Then
            Cells(CurrentRow, CurrentCol).Offset(0, 44) = 1
        End If
    Next CurrentRow
End Sub

Open in new window

0
 

Author Closing Comment

by:luminent
ID: 31570438
Thanks so much for understanding what I was asking. Not only did I learn how to actually include a better way of using a cell value for a date compare (which I've incorporated into a few other subroutineson the same project) but also a different way of moving through rows in a column than I had been doing. It's these little pieces of wisdom that you gather that really help in bridging the gaps between what books teach and the practical applications.
0
 
LVL 9

Expert Comment

by:felixdsouza
ID: 24154397
By the way, the line

Cells(CurrentRow, CurrentCol).Select

is not required.  I had inserted it for debugging purpose and forgot to remove it before posting the solution.  If you need to run the macro on a large number of rows of data, removing this line will speed up execution to a great extent.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question