Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Calculation in VBA of difference between TODAY and a date in another cell.

I have a cell with a date in it and I want to get a value that equates to the number of days from todays date.
So if the date is 5 days ago the it should return a number of -5, similarly todays date should return 0 and tomorrows date should return 1
1 Solution
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

with the date in A1, try


Make sure to format the result as General, not date!!

cheers, teylyn
Here's an example on how you can do this. I've putted the code on the Worksheet Change event so that when you enter a value on a cell on column A, it displays the number of days on the corresponding cell of column B.

Please check the attached file.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cll As Range
Dim rng As Range
Set rng = Range("A:A")

If Not Intersect(Target, rng) Is Nothing Then
    Target.Offset(0, 1).Value = Int(Format(Target.Value - Now(), Number))
End If

End Sub

Open in new window

Rory ArchibaldCommented:
Basically in VBA, just:
Range("A1").value - Date

Open in new window

GordonMassonAuthor Commented:
That works for me ...Thanks

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now