• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Using DateDiff

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
daiwhyte
Asked:
daiwhyte
  • 8
  • 6
  • 3
  • +2
1 Solution
 
Peter KwanCommented:
Please use the following:

DateDiff("yyyy", Cells(2, 20), Now()) & "/" & (DateDiff("m", Cells(2, 20), Now()) Mod 12)
0
 
jppintoCommented:
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
 
prashanthdCommented:
try this code

DateDiff("yyyy",cell(2, 20),Now) &"/"&(DateDiff("m",cell(2, 20),now) Mod 12)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
daiwhyteAuthor Commented:
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
 
patrickabCommented:
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
 
Peter KwanCommented:
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
 
daiwhyteAuthor Commented:
@patrickab - its the vba datediff function Im working on.
0
 
daiwhyteAuthor Commented:
@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
 
daiwhyteAuthor Commented:
For your info, the original date format is dd/mm/yyyy not sure if that makes a difference
0
 
Peter KwanCommented:
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
 
Peter KwanCommented:
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
 
daiwhyteAuthor Commented:
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
 
Peter KwanCommented:
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
 
Peter KwanCommented:
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
 
daiwhyteAuthor Commented:
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
 
patrickabCommented:
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
 
daiwhyteAuthor Commented:
Im in the office tomorrow and will run through the code, thanks in advance.
0
 
daiwhyteAuthor Commented:
This has worked a treat, thank you Patrick.
0
 
patrickabCommented:
daiwhyte - Thanks for the grade - Patrick
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now