CODEWITHSUNDEEP
vbaexceldatesubtraction
Philip Connell
Philip Connell

Reputation: 651

VBA To subtract today's date from a date in another column and return a number

I hope you can help. I have a small piece of code below. The issue I am having is that I am trying to subtract today's date from the date in Column C (see Pic 1) and then return a numerical result in Column D and then continue this formula down Column D until there is no values left in column C to subtract against.

So today's date is 09/03/2017 I want to subtract this date from the date in C2 03/07/2017 giving me 2 in D2 and then continue this through column D until C has a blank cell.

The piece of code that is bugging is Range("D2").Formula = DateDiff(C2, Date, "d")

The error I get is run time error 13 type mismatch.

The larger piece of code it belongs to is 

Public Sub Activate_Sheet()



Worksheets("In Progress").Activate
Columns.AutoFit
Range("N:N").EntireColumn.Delete
Range("D1").Value = "# days open"
Range("D2").Formula = DateDiff(C2, Date, "d")

End Sub

As always any and all help is greatly appreciated.

Pic 1

enter image description here

Upvotes: 1

Views: 7479

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

DateDiff function Parameters:

  • Interval in your case "d" (represnting days), is the first parameter, not the third.

You can't use C2 inside the DateDiff function, but you need to get the value from that cell by using Range("C2").Value.

Also, DateDiff will return a Numeric result in days, so you need to enter it in Range("D2").Value and not Formula.

Modify your code to:

Range("D2").Value = DateDiff("d", Range("C2").Value , Date)

Edit 1: To run this code for all occupied cells in Column C:

Dim LastRow  As Long, i As Long
With Worksheets("In Progress")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

    For i = 2 To LastRow
        .Range("D" & i).Value = DateDiff("d", .Range("C" & i).Value, Date)
    Next i
End With

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

Instead of 

Range("D2").Formula = DateDiff(C2, Date, "d")

use

Range("D2").Formula = "=DAYS(TODAY(),C7)"

.Formula has to be a formula as you write it into a cell (for english Excel versions). If you have a non-english (localized) Excel version then you can use .FormulaLocal to write formulas in your localized language.

Upvotes: 2

Related Questions