CODEWITHSUNDEEP
excelms-accessexcel-formula
user1342164
user1342164

Reputation: 1454

Convert an Excel formula to an Access query

Is there a way to convert the below Excel formula to a query/criterion in Access? 

L2 = Date
J2 = Another Date
Z1 = Todays Date

I think it is calculating the number of days in between two dates but not sure how to do this in an Access query. 

IF((AND((L2<1),(J2>1))),(NETWORKDAYS(J2,$Z$1)-1),0)

Upvotes: 0

Views: 214

Answers (3)

Ronak
Ronak

Reputation: 1

If you want to calculate number of days between days than you can use below built in function "DateDiff" which will solve it out. Datediff("D",,) Ex. Datediff("D",L2,Z1)

Upvotes: 0

ASH
ASH

Reputation: 20342

Set a reference to Excel.

Option Compare Database

Private Sub Command2_Click()
    MsgBox GetNetWorkDays(#1/29/2017#, #2/8/2017#)
End Sub

Function GetNetWorkDays(startDate As Date, endDate As Date) As Integer
GetNetWorkDays = WorksheetFunction.NETWORKDAYS(startDate, endDate)
End Function

Put the button on a Form and click the button!

Upvotes: 0

pnuts
pnuts

Reputation: 59485

As you may by now have gathered, the question does not really make sense. NETWORKDAYS does indeed calculate the number of “whole working days excluding weekends and any dates identified in holidays” between two dates (here whatever date is in J2 and whatever is ‘Today’/Z1) and IF makes that calculation contingent upon the result of the AND function being TRUE. The AND function results in TRUE provided both the ‘date’ in L2 is before 1/1/1900 and the date in J2 is after 1/1/1900 (because to Excel 1/1/1900 is Day 1 where dates are concerned [unless one opts for the 1904 date system]).

But Excel does not recognise a date before Day 1, whether a negative number or a decimal number. For example Day 1.5 is noon on 1/1/1900 whereas in the same format Day 0.5 shows as 0/1/1900.

So in essence, L2 is not a date of any real use to the formula, hence I believe why no answer yet to your question. But this is too long to fit in a comment hence my answer: “No.”!

Upvotes: 1

Related Questions