• 1

# Calculate Week Number in Formula Field

## Question

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.

## Recommended Posts

• 2
Hi NeoInJS,

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 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 on other sites

• 0
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!

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.