PDA

View Full Version : Today's date and following 6 working days



Ray.Mason
03-28-2013, 03:56 AM
I have submitted something similar and I thought I had it working but I'm still having issues.

This code below is in main macro and uses Function (further below) and should pick dates that fit in that range i.e. today or following 6 days. Note cell J1 is today's date.

For Each rng In rngOutputColC.Offset(, 9).Cells
If Len(Range("B" & rng.Row).Text) Then
If TaskActive(Range("E" & rng.Row).Value) Then
..........

Function TaskActive(dtStartDate As Date) As Boolean
If dtStartDate = Application.WorksheetFunction.WorkDay(Range("J1").Value, 6) Then
TaskActive = True
Else
TaskActive = False
End If
End Function

My problem is, even if I have dates within range, they are not being picked up.
Can you please advise where I'm getting it wrong here?

Ray

p45cal
03-28-2013, 04:54 AM
If you're looking for something falling within a range of dates, then shouldn't you be asking if dtStartDate is between (and including?) J1 and J1+6 working days?
Perhaps something along the lines of:If dtStartDate <= Application.WorksheetFunction.WorkDay(Range("J1").Value, 6) And dtStartDate >= Range("J1").Value Then


If so you might be able to reduce the function to one line:Function TaskActive(dtStartDate As Date) As Boolean
TaskActive = dtStartDate <= Application.WorksheetFunction.WorkDay(Range("J1").Value, 6) And dtStartDate >= Range("J1").Value
End Function

Ray.Mason
03-28-2013, 05:05 AM
Genius p45cal. Literally copied and pasted your code and works like a dream.

Many thanks!

:)

Ray.Mason
03-28-2013, 03:31 PM
Genius p45cal. Literally copied and pasted your code and works like a dream.

Many thanks!

:)

Just hit another deadlock where my other function requires 2 parameters and am getting an Argument Not Optional error. I'm not quite sure how I can set this up?

Function recentwork(dtreleaseDate As Date, dthandoverdate As Date) As Boolean
If dtreleaseDate <= Application.WorksheetFunction.WorkDay(Range("J1").Value) And dtreleaseDate >= Range("J1").Value Then
recentwork = True
If dthandoverdate <= Application.WorksheetFunction.WorkDay(Range("J1").Value) And dthandoverDate > Range("J1").Value Then
recentwork = True
Else
recentwork = False
End If
End If
End Function

Ray.Mason
03-28-2013, 03:33 PM
My subroutine code is calling function is :
If recentwork(Range("E" & rng.Row).Value) Then

p45cal
03-28-2013, 04:16 PM
If you've written a function with 2 arguments, then you should provide both when you call it:If recentwork(Range("E" & rng.Row).Value,another.valueHere) Then BUT.. I doubt the function will work as you want it to. What, in English (not code), do you want the function to return in relation to the supplied arguments dtreleasedate and dthandoverdate. Will one of these arguments always be later than the other? If so, which?

mdmackillop
03-28-2013, 04:17 PM
If your function requires 2 dates, you must supply them both. if it can work with one then IsMissing can be used, but works only on Variants

Sub test()
Data1 = "MD "
Data2 = "MDM "
MsgBox MyTest(Data1)
MsgBox MyTest(Data1, Data2)
End Sub

Function MyTest(x, Optional y)
If IsMissing(y) Then
MyTest = x & x & x
Else
MyTest = x & y & x
End If
End Function

Ray.Mason
03-29-2013, 11:07 AM
If you've written a function with 2 arguments, then you should provide both when you call it:If recentwork(Range("E" & rng.Row).Value,another.valueHere) Then BUT.. I doubt the function will work as you want it to. What, in English (not code), do you want the function to return in relation to the supplied arguments dtreleasedate and dthandoverdate. Will one of these arguments always be later than the other? If so, which?

Thanks Guys! Sorry my laptop crashed and could not post reply.

What I want the code to do in plain English is;

Look in columns I and J and if any of dates in these columns is equal or greater than today then include in report;

This is what I now have in main sub to call function;
If recentwork (Range ("I" & rng.Row).Value, Range ("J" & rng.Row).Value) Then ....

And the function;
Function recentwork (dtreleaseDate As Date, dthandoverdate As Date) As Boolean
If dthandoverdate => Now() Or dthandoverdate => Now() Then
recentwork = True
Else
recentwork= False
End If

I don't get the original Argument Not Optional error but despite dates falling within range, the report is still not picking them up.

Alternatively today's date is also in J1 cell

Thanks!

p45cal
03-29-2013, 11:21 AM
Now() includes time information. Date doesn't. Try:
Function recentwork(dtreleaseDate As Date, dthandoverdate As Date) As Boolean
recentwork = dthandoverdate >= Date Or dtreleaseDate >= Date
End Function

Ray.Mason
03-29-2013, 11:51 AM
:bow: mdmackillop and p45cal, Thanks very much guys, Really appreciate it.
p45cal that has worked and it's working exactly as I want it to, now I can enjoy my holidays. Happy holidays!!!