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

x
  • 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
Thanks
0
GordonMasson
Asked:
GordonMasson
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

with the date in A1, try

=A1-TODAY()

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

cheers, teylyn
0
 
jppintoCommented:
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.

jppinto
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

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

Open in new window

0
 
GordonMassonAuthor Commented:
That works for me ...Thanks
0

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