Check whether a date is 3 days away from today

[Nirbhay Sharma]

Hi Forum

I am trying to create a macro for checking whether a service request date is within the turnaround period of 3 days. the problem statement is:

If day is Monday/Tuesday; then the date difference between today and the request date should be less than or equal to 5 days.
For other days; the date difference between today and request date should be less than or equal to 3 days.

To do this i am trying to do the following:

1) check if the date difference lies between the dates in column D and today is as per the problem statement and accordingly put values true/false in column E
2) transfer all the FALSE values to sheet 2

I am using the formula DATEDIFF to get the date difference. I have also uploaded a sample file for you guys. Can you help me out with this.

 

[Hui]

It looks like your dates are text not dates

You are best to convert them to dates
Then use the Networkdays.Intl function eg:
=NETWORKDAYS.INTL(D2,TODAY(),1)

 

[Nirbhay Sharma]

Hi Hui.

Thanks for your reply. I read about networkday.intl function and that should work for me.

But I am not able to convert the column D into an excel readable date format. I have tried the following:

1) extract the date part from cells in column D and try to use datevalue function
2) after extracting i tried to convert that dateformat using text function but its not working.

can you please suggest me a way out of this?

 

[Khalid NGO]

Hi Hui.

Thanks for your reply. I read about networkday.intl function and that should work for me.

But I am not able to convert the column D into an excel readable date format. I have tried the following:

1) extract the date part from cells in column D and try to use datevalue function
2) after extracting i tried to convert that dateformat using text function but its not working.

can you please suggest me a way out of this?

Hi,
Your dates are not real numbers, check this:
=DATE(20&MID(D2,FIND(" ",D2)-2,2),SUBSTITUTE(LEFT(D2,2),"/",""),SUBSTITUTE(MID(D2,FIND("/",D2)+1,2),"/",""))

This wll convert your text vales in real dates.
now apply what Sir Hui mentioned in above post.

Regards,

 

[Nirbhay Sharma]

hi khalid.
thanks for your input and i am able to get the result i want by using the combination of the two formulas inside excel but i am facing a problem while writing a macro for the same. the error i am facing is due to the " (double quotes) used inside the Range.formula function.

plz have a look at the code in this file and tell me what is wrong in it.

thanks again for your help.

 

[Jagdev Singh]

Hi Nirbhay

You can easily get these code recorded. The below code should do the trick for you.

Sub TimeDiff()

Dim cell As Range
Dim myRange As Range
Dim lastRow As Long

With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row

   
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=DATE(20&MID(RC[-1],FIND("" "",RC[-1])-2,2),SUBSTITUTE(LEFT(RC[-1],2),""/"",""""),SUBSTITUTE(MID(RC[-1],FIND(""/"",RC[-1])+1,2),""/"",""""))"
    Selection.AutoFill Destination:=Range("E2:E" & lastRow)

    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""DD-MM-YYYY"")"
    Selection.AutoFill Destination:=Range("F2:F" & lastRow)
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=DATEVALUE(RC[-1])"
    Selection.AutoFill Destination:=Range("G2:G" & lastRow)
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=NETWORKDAYS.INTL(RC[-1],TODAY(),1)"
    Selection.AutoFill Destination:=Range("H2:H" & lastRow)
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=DATE(20&MID(RC[-5],FIND("" "",RC[-5])-2,2),SUBSTITUTE(LEFT(RC[-5],2),""/"",""""),SUBSTITUTE(MID(RC[-5],FIND(""/"",RC[-5])+1,2),""/"",""""))"
    Selection.AutoFill Destination:=Range("I2:I" & lastRow)
   
 

End With

End Sub

 

[Khalid NGO]

hi khalid.
thanks for your input and i am able to get the result i want by using the combination of the two formulas inside excel but i am facing a problem while writing a macro for the same. the error i am facing is due to the " (double quotes) used inside the Range.formula function.

plz have a look at the code in this file and tell me what is wrong in it.

thanks again for your help.

Hi Sharma,
Sorry, can't help you, i am zero at VBA
Can you check the code posted by JD?

Regards,

 

[Deepak]

Is this..

Option Explicit

Sub TimeDiff_1()
    With ActiveSheet
        .Range("E2:E" & .Cells(.Rows.Count, 1).End(xlUp).Row).Formula = _
        "=NETWORKDAYS.INTL(DATE(20&MID(D2,FIND("" "",D2)-2,2),SUBSTITUTE(LEFT(D2,2),""/"","""")," & _
        "SUBSTITUTE(MID(D2,FIND(""/"",D2)+1,2),""/"","""")),today(),1)>10"
    End With

End Sub