CODEWITHSUNDEEP
vbaexceldate
Rhyfelwr
Rhyfelwr

Reputation: 329

Counting due date and days left until a set deadline

I am trying to make a macro that takes a deadline entered by the user into cell E2 and then checks whether compared to today's date the deadline has passed or not. Also I am trying to display a count in cell F2 showing how many days are left until the deadline, or how many days it's over deadline. I have the following code, but it doesn't count the days properly, and I am not sure if the deadline check functions properly:

Sub Test()
Dim DDate As Date
Dim Ndate As String
Dim DDays As String
Dim NDays As String
Dim LDays As String

Ndate = Format(Date, "yyyymmdd") 'current date
DDate = Range("E2") 'due date, as entered in the cell by user, 20180101 in this example)

If DDate < Ndate Then Range("G1") = "ERROR" 'calculate whether the due date (DDate) has passed, and do something if yes)

NDays = Format(Date, "dd")
DDays = Range("E2").Value.Format(Date, "dd")

LDays = NDays - DDays 'count how many days are left until the due date (or how many days it's over the due date)
Range("F2") = LDays 'put the number of days in cell F2
End Sub

Upvotes: 0

Views: 943

Answers (2)

5202456
5202456

Reputation: 962

If you want to go down the VBA route, I have amended the code:

Sub Test()
Dim DDate As Date

' Change the date value 20180101 entered in to excel date format, as yyyy/mm/dd
 Range("E2").TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True

DDate = Range("E2").Value

If DDate < Date Then 'calculate whether the due date (DDate) has passed, and do something if yes)

Range("G1") = "ERROR"

Else

Range("F2").Value = DDate - Date

End If

End Sub

Upvotes: 0

Jonathan
Jonathan

Reputation: 1015

I don't think you need VBA. Remember that Excel stores dates as the number of days expired since 00/01/1900.

Therefore, if you want to know whether the date in E2, for example, is before or after the deadline in A1, you can simply do a straight comparison =IF(E2<=A1,"Deadline not passed","Deadline passed").

The number of days before or since the deadline is simply the difference: E2-A1.

Edit: I only just noticed you want to compare to today's date. As Chris's comment suggests, you can use =TODAY() to get this.

Upvotes: 2

Related Questions