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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
Basically in VBA, just:
Range("A1").value - Date

Open in new window

GordonMassonAuthor Commented:
That works for me ...Thanks
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.

All Courses

From novice to tech pro — start learning today.