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
GordonMassonAsked:
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:
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

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

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