?
Solved

Using DateDiff

Posted on 2011-02-22
19
Medium Priority
?
417 Views
Last Modified: 2012-05-11
Hi,

Im trying to work out the a date by using the datediff function within a macro Im trying to write.

I have a spreadsheet which has a start date of a agreement, I want to use the datediff function to work out how long the agreement has been live.

This is what I got but its not working.

DateDiff(cell(2, 20), Now(), "yyyy") & "/" & DateDiff(cell(2, 20), Now(), "m")

In the cell (2,20) has a value of 22/02/2010

So when the macro runs it should replace this data with 1yr0mths or 1/0.
0
Comment
Question by:daiwhyte
[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
  • 8
  • 6
  • 3
  • +2
19 Comments
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 34951351
Please use the following:

DateDiff("yyyy", Cells(2, 20), Now()) & "/" & (DateDiff("m", Cells(2, 20), Now()) Mod 12)
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34951355
You're missing the reference to the object, in this case, to the sheet! And you have the wrong order of arguments on your DateDiff function!

You should try like this:

DateDiff("yyyy", ActiveSheet.Cells(2, 20).Value, Now()) & "/" & DateDiff("m", ActiveSheet.Cells(2, 20).Value, Now())
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 34951372
try this code

DateDiff("yyyy",cell(2, 20),Now) &"/"&(DateDiff("m",cell(2, 20),now) Mod 12)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:daiwhyte
ID: 34951474
Ok tried both but they errored, vb editor suggested changing the first "&" to an equals ??

Here is a copy of all the code in the macro, the datediff function is toward the bottom of the code.

Sub Employment()
Dim sName As String, blankrow As Integer, rownum As Integer
Dim rng As Range, cell As Range
sName = ActiveSheet.Name
blankrow = 0
rownum = 1
If sName = "" Then Exit Sub

'Set rng = Range(Range("P1"), Cells(Rows.Count, 1).End(xlDown))
While blankrow < 10
Set cell = Range(("P" & rownum))
 If cell.Text = "Full Time Employment" Then
    cell.Value2 = "Employed FT"
 ElseIf cell.Text = "Part Time Employment" Then
    cell.Value2 = "Employed PT"
 ElseIf cell.Text = "Temporary or Contract" Then
    cell.Value2 = "Self Employed"
 ElseIf cell.Text = "" Then
blankrow = blankrow + 1
DateDiff("yyyy", ActiveSheet.Cells(rownum, 20).Value, Now()) & "/" & DateDiff("m", ActiveSheet.Cells(rownum, 20).Value, Now())
End If
 rownum = rownum + 1
Wend
End Sub


0
 
LVL 45

Expert Comment

by:patrickab
ID: 34951516
Please note that DATEDIF() only has one F not 2! Also that its syntax is:

DATEDIF(startdate,enddate,interval type)

Interval types are as follows:

Interval       Meaning       Description
m       Months       Complete calendar months between the dates.
d       Days       Number of days between the dates.
y       Years       Complete calendar years between the dates.
ym       Months Excluding Years       Complete calendar months between the dates as if they were of the same year.
yd       Days Excluding Years       Complete calendar days between the dates as if they were of the same year.
md       Days Excluding Years And Months       Complete calendar days between the dates as if they were of the same month and same year.

For comprehensive explanation of DATEDIF() see:

http://www.cpearson.com/excel/datedif.aspx
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 34951565
daiwhyte, you should assign the return value to a variable or as a value of a cell for the datediff, like this:

Cells(1, 2) = DateDiff("yyyy", ActiveSheet.Cells(rownum, 20).Value, Now()) & "/" & (DateDiff("m", ActiveSheet.Cells(rownum, 20).Value, Now()) mod 12)
0
 

Author Comment

by:daiwhyte
ID: 34951594
@patrickab - its the vba datediff function Im working on.
0
 

Author Comment

by:daiwhyte
ID: 34951750
@pkwan

Ive slightly amended the syntax so I can see the results and they are all coming back as 112/2

Any ideas?  Also, is it not possible to append to the original cell?
0
 

Author Comment

by:daiwhyte
ID: 34951776
For your info, the original date format is dd/mm/yyyy not sure if that makes a difference
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 34951874
You mean like this?

Sub Employment()
Dim sName As String, blankrow As Integer, rownum As Integer
Dim rng As Range, cell As Range
sName = ActiveSheet.Name
blankrow = 0
rownum = 1
If sName = "" Then Exit Sub

'Set rng = Range(Range("P1"), Cells(Rows.Count, 1).End(xlDown))
While blankrow < 10
Set cell = Range(("P" & rownum))
 If cell.Text = "Full Time Employment" Then
    cell.Value2 = "Employed FT"
 ElseIf cell.Text = "Part Time Employment" Then
    cell.Value2 = "Employed PT"
 ElseIf cell.Text = "Temporary or Contract" Then
    cell.Value2 = "Self Employed"
 ElseIf cell.Text = "" Then
blankrow = blankrow + 1
cell.Value = DateDiff("yyyy", ActiveSheet.Cells(rownum, 20).Value, Now()) & "/" & (DateDiff("m", ActiveSheet.Cells(rownum, 20).Value, Now()) mod 12)
End If
 rownum = rownum + 1
Wend
End Sub

Open in new window


Remember to take the modular of 12 for months' difference. I tried myself and it works for Excel 2002 and above
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 34951901
Typo in the last comment. Please find the following:

Dim sName As String, blankrow As Integer, rownum As Integer
Dim rng As Range, cell As Range
sName = ActiveSheet.Name
blankrow = 0
rownum = 1
If sName = "" Then Exit Sub

'Set rng = Range(Range("P1"), Cells(Rows.Count, 1).End(xlDown))
While blankrow < 10
Set cell = Range(("P" & rownum))
 If cell.Text = "Full Time Employment" Then
    cell.Value2 = "Employed FT"
 ElseIf cell.Text = "Part Time Employment" Then
    cell.Value2 = "Employed PT"
 ElseIf cell.Text = "Temporary or Contract" Then
    cell.Value2 = "Self Employed"
 ElseIf cell.Text = "" Then
blankrow = blankrow + 1
cell.Value2 = cell.Value2 & DateDiff("yyyy", ActiveSheet.Cells(rownum, 20).Value, Now()) & "/" & (DateDiff("m", ActiveSheet.Cells(rownum, 20).Value, Now()) mod 12)
End If
 rownum = rownum + 1
Wend
End Sub 

Open in new window

0
 

Author Comment

by:daiwhyte
ID: 34952034
not quite. The data is being inserted in the P and its still showing 112/2

The Column/Row which has the start date which we are applying the DateDiff is T,1

So ideally, I would like to read whats in T1 then change it to the datediff.

I attached the spreadsheet and have stripped out all unwanted data.

Example.xls
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 34952131
Are you wanting when the T column of a row is not empty, then we print it in the same row? Like this:

Sub Employment()
Dim sName As String, blankrow As Integer, rownum As Integer
Dim rng As Range, cell As Range
sName = ActiveSheet.Name
blankrow = 0
rownum = 1
If sName = "" Then Exit Sub

'Set rng = Range(Range("P1"), Cells(Rows.Count, 1).End(xlDown))
While blankrow < 10
Set cell = Range(("P" & rownum))
 If cell.Text = "Full Time Employment" Then
    cell.Value2 = "Employed FT"
 ElseIf cell.Text = "Part Time Employment" Then
    cell.Value2 = "Employed PT"
 ElseIf cell.Text = "Temporary or Contract" Then
    cell.Value2 = "Self Employed"
 ElseIf cell.Text = "" Then
    blankrow = blankrow + 1
End If
If ActiveSheet.Cells(rownum,20).Value <> "" Then
 cell.Value2 = cell.Value2 & DateDiff("yyyy", ActiveSheet.Cells(rownum, 20).Value, Now()) & "/" & (DateDiff("m", ActiveSheet.Cells(rownum, 20).Value, Now()) Mod 12)
End If
 rownum = rownum + 1
Wend
End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 34952170
The following would handle the title row as well:

Sub Employment()
Dim sName As String, blankrow As Integer, rownum As Integer
Dim rng As Range, cell As Range
sName = ActiveSheet.Name
blankrow = 0
rownum = 1
If sName = "" Then Exit Sub

'Set rng = Range(Range("P1"), Cells(Rows.Count, 1).End(xlDown))
While blankrow < 10
Set cell = Range(("P" & rownum))
 If cell.Text = "Full Time Employment" Then
    cell.Value2 = "Employed FT"
 ElseIf cell.Text = "Part Time Employment" Then
    cell.Value2 = "Employed PT"
 ElseIf cell.Text = "Temporary or Contract" Then
    cell.Value2 = "Self Employed"
 ElseIf cell.Text = "" Then
    blankrow = blankrow + 1
End If
If ActiveSheet.Cells(rownum, 20).Value <> "" And IsDate(ActiveSheet.Cells(rownum, 20).Value) Then
 cell.Value2 = cell.Value2 & DateDiff("yyyy", ActiveSheet.Cells(rownum, 20).Value, Now()) & "/" & (DateDiff("m", ActiveSheet.Cells(rownum, 20).Value, Now()) Mod 12)
End If
 rownum = rownum + 1
Wend
End Sub

Open in new window

0
 

Author Comment

by:daiwhyte
ID: 34952198
This is erroring when running on the line you added.

Yes if the cell is not empty then transform the data in that cell using the datediff fucntion

So if the cell has 22/02/2010 then it needs to show 2 / 0

DW
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 34953021
daiwhyte,

The code below is in the attached file. Press the button on Sheet1 to run the macro.

Patrick
Sub Employment()
Dim rownum As Long
Dim rng As Range
Dim cell As Range

rownum = 1
ActiveSheet.Columns("B").ClearContents
ActiveSheet.Columns("D").ClearContents
While Cells(rownum, "A") <> ""
    Set cell = Range(("A" & rownum))
    Select Case cell
        Case Is = "Full Time Employment"
            cell.Offset(0, 1) = "Employed FT"
        Case Is = "Part Time Employment"
            cell.Offset(0, 1) = "Employed PT"
        Case Is = "Temporary or Contract"
            cell.Offset(0, 1) = "Self Employed"
    End Select
    If ActiveSheet.Cells(rownum, 4) = "" And IsDate(ActiveSheet.Cells(rownum, 3)) Then
        cell.Offset(0, 3) = DateDiff("yyyy", ActiveSheet.Cells(rownum, 3), Now()) & " / " & (DateDiff("m", ActiveSheet.Cells(rownum, 3), Now()) Mod 12)
    End If
    rownum = rownum + 1
Wend

End Sub

Open in new window

dates-01.xls
0
 

Author Comment

by:daiwhyte
ID: 34998871
Im in the office tomorrow and will run through the code, thanks in advance.
0
 

Author Closing Comment

by:daiwhyte
ID: 35067932
This has worked a treat, thank you Patrick.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35072291
daiwhyte - Thanks for the grade - Patrick
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

764 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