Jump to content
  • 1
NeoInJS

Calculate Week Number in Formula Field

Question

2 answers to this question

Recommended Posts

  • 2
Hi NeoInJS,
 
Please try using this formula:
FLOOR(
    (
        DATEDIFF(day, CONVERT(datetime, CAST(DATEPART(year, [@field:Start_Time]) AS CHAR(4)) + '-01-01', 101), [@field:Start_Time]) + 
        (
            7 - 
            (
                (
                    (
                        (
                        DATEPART(day, [@field:Start_Time]) + 
                        FLOOR(13 * ((CASE WHEN DATEPART(month, [@field:Start_Time]) < 3 THEN DATEPART(month, [@field:Start_Time]) + 12 ELSE DATEPART(month, [@field:Start_Time]) END) + 1) / 5) + 
                                   ((CASE WHEN DATEPART(month, [@field:Start_Time]) < 3 THEN DATEPART(year,  [@field:Start_Time]) - 1  ELSE DATEPART(year,  [@field:Start_Time]) END) % 100) + 
                             FLOOR(((CASE WHEN DATEPART(month, [@field:Start_Time]) < 3 THEN DATEPART(year,  [@field:Start_Time]) - 1  ELSE DATEPART(year,  [@field:Start_Time]) END) % 100) / 4) + 
                              FLOOR((CASE WHEN DATEPART(month, [@field:Start_Time]) < 3 THEN DATEPART(year,  [@field:Start_Time]) - 1  ELSE DATEPART(year,  [@field:Start_Time]) END) / 400) + 
                               5 * ((CASE WHEN DATEPART(month, [@field:Start_Time]) < 3 THEN DATEPART(year,  [@field:Start_Time]) - 1  ELSE DATEPART(year,  [@field:Start_Time]) END) / 100)
                        ) % 7
                    ) + 6
                ) % 7 + 1
            )
        )
    ) / 7
) + 1
 
NOTES:
  1. You must replace all instances of [@field:Start_Time] with your actual field.
  2. You can test for correctness by adding a calculated field that uses DATEPART(week, [@field:Start_Time]).

Share this post


Link to post
Share on other sites
  • 0

Brilliant nightowl! You're a star! Thanks for sharing! It seems a little long winded, but it works.

It's a pity Caspio doesn't have a simple formula like "Week" or "Weeknum" just like it has "Year" or alternatively enable the formula "DatePart(week,[@field:Date])" it suggests exists in the following article: https://howto.caspio.com/function-reference/ :)

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×