CODEWITHSUNDEEP
excelvba
Karim_K
Karim_K

Reputation: 15

Calculate how many days have passed since user's input date

I am trying to let the user input a Date in a cell. Using the input date and today's date, I would calculate how many days have passed since the user's input date.

Example: User inputs "2/15/2019" in cell (C4) - The next day it shows "1 Day/s" and so on.

I've searched and tried many things but failed, I am no expert with excel or VBA by any means, so I am not even sure if that is possible.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next   
    If Not Intersect(Target, Range("L:L")) Is Nothing Then
        With ActiveWorkbook.Worksheets("Ended")
            Application.EnableEvents = False
            Target = "Today()" - Target
            Application.EnableEvents = True
        End With
    End If

This issue can be solved by this formula, however I can't apply it using VBA to user input date.

=TODAY()-DATEVALUE("15/2/2020")

Upvotes: 1

Views: 301

Answers (1)

ACCtionMan
ACCtionMan

Reputation: 511

I don't have enough reputation to comment yet, so I'll try to answer based on what you've said.

You were on the write track, just need to get it to enter a formula using Target.Formula.

I've often struggled with using dates in Excel formulas. The easiest way is to use the numeric value of the date. You can do this by converting the date to a Long using CLng().

Complete line of cod reads

Target.Formula = "=Today() - " & CLng(Target)

As others have also mentioned, you are going to be writing back a number into a cell that will have a date format after the user enters the date. For example, if I enter 01/02/2020 (dd/mm/yyyy is my local date format) into a cell, it will set Target to equal 16/02/2020 - 01/02/2020 which is 15 days, however the cell will have 15/01/1900 (computer dates start at 01/01/1900) unless you change the format of the cell to a number. You can do this using

Target.NumberFormat = "0"

Upvotes: 1

Related Questions