• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • 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 KwanAnalyst ProgrammerCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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 KwanAnalyst ProgrammerCommented:
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 KwanAnalyst ProgrammerCommented:
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 KwanAnalyst ProgrammerCommented:
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 KwanAnalyst ProgrammerCommented:
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 KwanAnalyst ProgrammerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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