Solved

Using DateDiff

Posted on 2011-02-22
19
408 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 500 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now