MyNetWorkDays function - Visitor Forum


	Back to Visitor Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

MyNetWorkDays function

Ben Davis

2 years ago

Hi, If this needs to be an additional post i understand, if it can be an comment furthering my previous post then great.

I'm now attempting to use this function in a query to check for the working day difference between the fields "Finished" and "DueDate"; the expression i am using is "TAT: X([Finished],[DueDate])".

My issue is that the Finished date can sometimes be after the DueDate, meaning the DueDate wasnt met. I would like this to be displayed as a negative value but the function isnt allowing for that, if the finished date lands after the due date then it is simply returning the value of "0".

Would this be a simple use of an IIF function within the expression to check if the DueDate is more or less than the Finished date and then return the corresponding value? If so how would i ask it to display negative number in the aforementioned scenario; or is there a simpler way?

Kind regards,
Ben

	Kevin Robertson @Reply 2 years ago
You could add the following line of code to the end of the Function: `If MyNetWorkDays = 0 Then MyNetWorkDays = EndDate - StartDate`

Ben Davis OP @Reply

2 years ago

Comment to be added

Hi Kevin, i added that line to the end of the function code so that the function code looks like this
"Function X(StartDate As Date, EndDate As Date) As Long

    X = 0
    D = StartDate
    While D <= EndDate
        If Weekday(D) > 1 And Weekday(D) < 7 Then X = X + 1
        D = D + 1
    Wend

    If X = 0 Then X = Endate - StartDate
End Function"

I used the expression like this "TAT: X([Finished],[DueDate])".

As previously mentioned this worked when Finished was before DueDate. However if it was the other way round it was giving me results in the -45000's. For instance, Finished was 2nd April 2024, DueDate was 13th March 2024, the expression gave me the result -45384.

Is it possible that the code for the Function itself needs you to tell it which way round to put the dates at the very start?

I.E. If StartDate < EndDate then Follow established code
If not follow new code where StartDate and EndDate are switched?

Or would this not work?

Thanks again,
Ben

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Visitor Forum.

Next Unseen

New Feature: Comment Live View