Date Range from WEEKNUM

I understand that WEEKNUM can pull the week number from a given date, my question is, If I can provide the WEEKNUM, can I generate a date range based off my WEEKNUM? How would I go about this?

0 29 1,987
29 REPLIES 29

Could you please try the below expression? It will give the first date of the week (Sunday per AppSheet day/date functions standards. for Monday as first day, please add 2 in the end of expression instead of 1)

EOWEEK(TODAY())+ ([WEEKNUMBER]-WEEKNUM(TODAY())-1)*7 +1

[WEEKNUMBER] column gives the week number input. By the nature of expression, it will work only for the current year (currently 2021) . The expression will become that much more complex if the week numbers belong to different years.

Please test well.

For getting other days of the week, one may need to construct a list by adding 2, 3, โ€ฆ7 in the end of the expression.

So Saturday ( last day of the week ) date will be

EOWEEK(TODAY())+ ([WEEKNUMBER]-WEEKNUM(TODAY())-1)*7 +7

Thank you kind sir, im trying to decode logically the expression which already blew my mind

I will update you when i put into my app thank you for your effort either way!

Itโ€™s saying invalid input type: Unknown I tried changing he column types making the [weeknum] column as Number

Please post screenshot of your expression , where you are using it and column type ( input column)

im starting to wrap my mind around the concept though ima see if i can fool with it a little

Please try changing the Weeknum column name to WEEKNUMBER or something that is different from [Weeknum]

Weeknum could be a reserved word, since it is used in function WEEKNUM()

I tested again and it works for me.

Results

column type weeknumber = Number?

Yes, correct.

You are correct sir, God bless

What I am going for, is a UI that allows user to โ€˜scrollโ€™ through weeks, then i would have slices that would display the information for that week. So we work off a point system, and you generate points by doing tasks and working in the office. The points represent a % of profit.

All that aside, im trying to have a counter or somethign so if i +1 the WEEKNUM that it will display the weekโ€™s info that im selecting

Maybe i have a table that has the weeks then i can just reference it?

Hi @Suvrutt_Gurjar ,

What would I need to make this work for different years?
and
What changes to the expression would there be to make this work also?

Best,
Chris.

Hi @Chris_Jeal ,

By nature WEEKNUM() results are restricted to a year. 1 to 52 or 53.

So the expression will require year as well as week input for multiple years computation. Not sure how complex or easy the resulting expression will be but could you update if you will be supplying these two inputs to compute the range? Also is the date range required for one week or some extended range?

Thanks @Suvrutt_Gurjar,

I will be adding these two inputs into the structure and the date range could well be more than one week.

This will be helping towards phase planning within a project management tool.

Thanks again.

Thank you @Chris_Jeal

So is it right to say you will add any Week number ( input 1), in any year (input 2) and you would like the date range computed? In case you wish it multiweek range, it will need input 3 for the range in weeks?

Could you elaborate?

Hi @Suvrutt_Gurjar,

Apologies for the delay in replying.

Absolutely, yes; I think that using 3 inputs (year | week from | week to) would be the best course of action to cover off any long and short term projects.

This would enable me to create a roadmap of projects and possibly mini roadmaps within these.
I have managed to this one below working but this will only cover off months not weeks and will not allow for entries to start in one year and cross over to the next.

Best,
Chris.

Thank you. I think the third input will need to be in terms of the number of weeks rather than the week to ( as WEEKNUMBER) because week numbers roll over after 52 to 53. So it will be a bit difficult to decipher if the " week to" number is to be considered for next year or this year.

I will revert if I come across anything feasible I can come up with.

BTW the picture you have shared looks nice. May we know how did you configure it?

Thanks for that @Suvrutt_Gurjar,

I took inspiration from the post below and added my own code for each month column:

if(
and(
month([Start date])<2,
year([Start date])=year(today()),
month([Due date])>1,
year([Due date])=year(today())),

CONCATENATE(โ€œdata:image/svg+xml;utf8,



โ€œ),โ€โ€)

That is nice @Chris_Jeal. Thank you for the update.

Hi @Suvrutt_Gurjar,

Iโ€™ve been wracking my brains on how to get this to work across years maybe even using USERSETTINGS() but still cannot figure it out.

I wonder if you have anything up your sleeve?

Best,
Chris

Hi @Chris_Jeal ,

Sorry, I had not tried anything till your post today, Have tried a few related expressions now. Hope it helps.

The column names and associated expressions are summarized below

One test case in test app below

Chris_Jeal
Participant V

Hi @Suvrutt_Gurjar,

Many thanks for helping out (again), forgive my lack of understanding, but I wonder how would the output fit into my Month columns which make up the Gantt chart?

The code for February for example is below:

if(
and(
month([Start date])<3,
year([Start date])=year(today()),
month([Due date])>2,
year([Due date])=year(today())),
CONCATENATE("

data:image/svg+xml;utf8,
<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 100 100'>
   <rect x='0' y='0' height='100' width='400' fill='blue'/>
</svg>

โ€œ),โ€")

What I would like to achieve is the Gantt chart following on to the next year if the dates from and to cross over the year.
I may need to use USERSETTINGS() to select the year which is preferable in a way that when each year is chosen I could possibly have the desired effect as below.

August 2021 - Feb 2022

Thanks for your time.
Best,
Chris.

Hi @Chris_Jeal

Will there be a different row for each project for each year? How are these rows created for each project for each year- meaning how a new record is inserted? Is there one column for each month in the table that depicts the Gantt chart ?

Maybe you wish to have a time window of three years on the Gantt chart so that typically projects running over a year and rollong into next calendar year could be shown comfortably instead of creating new rows for a project every year. Creation of a new row for every year and every project in the Gantt chart could in general be more complex.

The video below capture project timelines from 2020 (previous year), 2021( current year) and 2022 (next year).

When calendar year 2022 starts the timeline will automatically change to 2021, 2022, 2023.

The idea is, typically projects older than last calendar year are not likely to be active. The timeline can be made to appear only for those months where projects are active with a slice. With this slice approach, one need not show all the 36 months but only those months where projects are active.

Thanks @Suvrutt_Gurjar,

It makes sense to me to use your approach and slice out any projects that arenโ€™t active any longer, also I donโ€™t see any issue extending the range of months.
Iโ€™m about to present the concept Iโ€™ve got to our team but would like to incorporate what you have shown in the short video.

Would there be any chance I could make a quick copy of what you have to enable me to make the changes needed to use some of these elements?

Best,
Chris.

Sure @Chris_Jeal . I will share the test app with you. Hope your email ID to share the app is same as that you had shared last time. Can you please confirm?

Many thanks @Suvrutt_Gurjar, thatโ€™s correct the email address hasnโ€™t changed.

Top Labels in this Space