Calculate Week Number in Formula Field

[NeoInJS]

Hello - I have a field in my table that has a Formula Field. I need help to generate a formula that will give me the week number based on my TimeStamp field.

 

[nightowl]

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]).

[Stepford]

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/ 

 

[ManokNaPula]

On 3/22/2018 at 1:54 AM, Stepford said:

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/ 

  

Actually, you can use a Datediff for week.

Datediff(week, GetUTCDate(), [@field:StartTime])

 

I tried and it did work for me.

 

You can also check out Caspio's latest release. Click this link to find out more.

 

Hope this helps!

[Kronos]

Hi! adding these howto articles in case it might help you:

https://howto.caspio.com/datapages/reports/advanced-reporting/calculated-fields-and-datediff-function/

https://howto.caspio.com/function-reference/#:~:text=Date and Time Functions 

The second one states that DatePart(week, date), SysDateTime() and SysteUTCDateTime() functions will not work in the formula field.

[Kurumi]

Hi - If you would like to show the week number of the year like now (It is currently week 39 in 2023) - this can be done by using calculations.

To use it in Calculated Value, you can use this formula:

Datepart(week,SysDateTime())

If you would like to display it in the HTML Block (Details Page), you may use a Calculated Field with this formula:

Datepart(week,SysDateTime()) - 1

Hope it helps!

[Queso]

Hi, you can also use Application Task automation tool to show the week number of the date or timestamp. The following is an example: