Formula to calculate week of month completed

[bwlytkr]

I tried this formula in Access to calculate the week of the month the task was completed and it doesn't calculate correctly. Any thoughts on the formula I can use to do this. It's the standard 3/17/2020 date format.

Completed Week: Format([Completed Dt],"w""Week")

 

[bwlytkr]

I had a lot of viewers but, no response. Does that mean their is no access formula for this? I thought if there was an excel formula it could be converted into an access formula. Not True?

 

[Joe4]

I had a lot of viewers but, no response. Does that mean their is no access formula for this? I thought if there was an excel formula it could be converted into an access formula. Not True?

Not necessarily. All threads with no replies show up in the "Zero Replies" listing. Many people look there for unanswered questions. However, many people are unaware that Access questions show up there too (not just Excel questions). So, they might have throught it was an Excel question, until they opened it up and read the details. And I would venture to say that most users here don't use Access.

Can you post the Excel version of the formula that you have that is working? I want to re-create it and see exactly what it does.

 

[welshgasman]

What did we do before Google?

Try

Completed Week: Format([Completed Dt],"ww" )

DatePart Function

Format function (Visual Basic for Applications)

I am not even sure what the last parameter was meant to do?

 

[bwlytkr]

The formula from Welshgasman gives me the week of the year. I am looking for the week 1-5 that it falls in for each month. I use this formula in excel and it seems to work fine.

=ISOWEEKNUM(A1)-ISOWEEKNUM(DATE(YEAR(A1),MONTH(A1),1))+1

 

[Joe4]

Check out the methods shown here: MS Access get ISO standard week number

 

[welshgasman]

The formula from Welshgasman gives me the week of the year. I am looking for the week 1-5 that it falls in for each month. I use this formula in excel and it seems to work fine.

=ISOWEEKNUM(A1)-ISOWEEKNUM(DATE(YEAR(A1),MONTH(A1),1))+1

So could you not use Weeknumber of your date - weeknum of 1st of your month +1 ?

That appears to be what that Excel formula is calculating?

 

[bwlytkr]

yes, the excel formula is giving me the week of the month which is what I am trying to get a formula for in Access.

This formula Completed Week: Format([Completed Dt],"ww" ), is giving me the week of the year

 

[welshgasman]

So use the DatePart function to calculate the week, exctly the same way you are doing it in Excel?

 

[bwlytkr]

It's the same date format in access as it is in excel