Formula for determining the week

Hi Everyone,

I have a grid in which all my truck data goes in every day. For the data that goes into that grid everyday, I have created a column in which I want it to tell me what week of the month it is.

I would also like my weeks to work from Sunday to Saturday.

So basically for June it shouldve been as follows:

1-6th = week 1

7-13th = week 2

etc

and for July it should've been as follows:

1-4th = week 1

5-11th = week 2

Attached below is a simple excel file of how my grid looks. It should help you get a better understanding.

So essentially I need help with a formula to go into the orange strip, and then someone to please help me automate the table to the right as well

Regards

Rainier

Find more posts tagged with

Accepted answers

Paul Newcome

I made a slight modification to the solution in the above linked thread.

=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1 + IF(WEEKDAY(Date@row) = 1, 1)

Give that a go and see how it works.

All comments

Paul Newcome

This has actually been solved somewhere here in the Community. I just seem to be having trouble locating the thread.

@L@123, weren't you the one to help in the other thread where we were trying to figure out how to display the week of the month?

Paul Newcome

Take a look through THIS THREAD. I believe there is a solution for what you are wanting to do.

Rainier Hollands

@Paul Newcome

Hi Paul, unfortunately that thread doesnt help, as I'm looking at strating my week on a Sunday to a Saturday.

I also then need help in counting the amount of days in that week.

Regards

Rainier

Rainier Hollands

@Paul Newcome

Hi Paul,

I really need help with this. It's relatively urgent for a client.

Paul Newcome

Sorry about that. Work has been pretty busy. I have a sheet set up to do some testing when I am able.

Paul Newcome

I made a slight modification to the solution in the above linked thread.

=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1 + IF(WEEKDAY(Date@row) = 1, 1)

Give that a go and see how it works.

Rainier Hollands

@Paul Newcome

Hi Paul,

You are an absolute legend!!!!!!!! Thank you so much! Works exactly how I wanted it to.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.