week range

cray · 04-25-2023 02:41 PM

create week range field that displays the mm/dd/yy - mm/dd/yy by user choosing current week, previous week or next week

cray

I did this
=IF([Select Week] = "Previous Week", TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - WEEKDAY(TODAY()) - 6), "M/D/YYYY") & " - " & TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - WEEKDAY(TODAY())), "M/D/YYYY"), IF([Select Week] = "Current Week", TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - WEEKDAY(TODAY()) + 1), "M/D/YYYY") & " - " & TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - WEEKDAY(TODAY()) + 7), "M/D/YYYY"), IF([Select Week] = "Next Week", TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - WEEKDAY(TODAY()) + 8), "M/D/YYYY") & " - " & TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - WEEKDAY(TODAY()) + 14), "M/D/YYYY"), "")))
but it keeps telling me that the date function is used incorrectly.

cray

IF([Select Week] = "Previous Week", TODAY() - 7,
IF([Select Week] = "Current Week", TODAY(),
IF([Select Week] = "Next Week", TODAY() + 7, "")
)
)

that one works, but it only shows one day/date not the range and if the current day is not the week start day then adding or subtracting 7 is not going to give me the correct week start date for the range.

Suvrutt_Gurjar

You may want to add what you mean by range. In what format you are looking for range and you may also want to add what purpose you are using the range so that a better suggestion can be given.

By range do you mean just displaying start and end date 04/23/2023- 04/29/2023 for the current week or do you want to display all the seven dates in in the week?

Please also add what type of column you are using to show this range.

Edit: Till the time you revert on the above queries, it sounds based on the expressions you shared that you are simply looking for week start and week end date concatenated together in the format 04/23/2023- 04/29/2023

If so, please try the expression below

SWITCH([Select Week],

"Previous Week", CONCATENATE( TEXT(EOWEEK(TODAY()-14)+1,"M/D/YYYY"),"-",TEXT(EOWEEK(TODAY()-7),"M/D/YYYY")),

"Current Week", CONCATENATE( TEXT(EOWEEK(TODAY()-7)+1,"M/D/YYYY"),"-",TEXT(EOWEEK(TODAY()),"M/D/YYYY")),

"Next Week", CONCATENATE( TEXT(EOWEEK(TODAY()+1)+1,"M/D/YYYY"),"-",TEXT(EOWEEK(TODAY()+7),"M/D/YYYY")),
""

)

The above expression will have week start day as Sunday and week end day as Saturday as per AppSheet week convention. The column type for the expression is text type. We can suitably add /delete days in the expression to create weeks starting at different days such as Monday-Sunday and so on.

Please test well.

EOWEEK() - AppSheet Help

SWITCH() - AppSheet Help

cray

Sorry for being vague before.

This is a timesheet. It will first identify the user based off login which will display their name and use their hourly rate to calculate in bg after info is sent. Next will have submission date which will automatically be current date. Next will have a week range with default as current week but option to change to previous weeks or following weeks. You are correct the range would be 4/24/23-4/30/23 style however I would want the week start on Monday and end on Sunday. Then the button that shows prevoious current and next for being able to change week.

after that there should be fields for Monday through Sunday that allows user input for adding or subtracting hours. Next to each weekday the date that matches the day of week based on the week range chosen before should be right there on same line as the weekday name. For example Monday 4/24/23 Tuesday 4/25/23.

Then there will be a few categories that won't count toward the hours in the total count that they will add roughly how much time they spent on those categories daily.

Where I ran into trouble was in the spot where the user should be able to put in hours it was showing the day of week & date and the title of the field was the day of week as well but not with date. So it was catching part of my code but it was going in the wrong place. Second issue is I could never get the week range to show. It would always have no range until I hit one of the 3 buttons and then in the range in would just show one date rather than a range. and then the weekday fields would open up but instead of putting hours the thing I mentioned above was already in field and greyed out.

My columns are Employee Name, Week Range, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Hourly Rate, Total Hours, Selected Week, Monday Date, Tuesday Date, Wednesday Date, Thursday Date, Friday Date, Saturday Date, Sunday Date and Position. The week range formula with data type date is :IF([Selected Week] = "Previous Week", TODAY() - 7,
IF([Selected Week] = "Current Week", TODAY(),
IF([Selected Week] = "Next Week", TODAY() + 7, "")
)
)
Selected Week is enum and no formula just values of Previous Week, Next Week, and current Week.

Monday Date is date IF(ISBLANK([Week Range]), "",
[Monday Date] + 1
)

Tuesday Date date IF(ISBLANK([Week Range]), "",
[Monday Date] + 1
) and so on. That's the last I've tried, but many other iterations too. When I would get one thing working formula wise it wouldn't do what it was supposed to in preview mode. Hope this helps explain better.

Suvrutt_Gurjar

Thank you for the details. But I am sorry that, I could not understand much as to exactly what you are trying to accomplish through Week Range.

Also your column / data structure appears a bit different because you have mentioned Monday....Sunday as column names as well as Monday Date...... Sunday Date as column names. It is also not clear how you will be handling multiple weeks.

Anyway the community is generally more suited for queries related to specific expressions or feature etc. so we could look into week range related query.

In general, I feel that you would be able to use EOWEEK() function to derive the week ranges as the EOWEEK() zeroes down on a Saturday date. From that Saturday date you could conveniently compute date of other weekdays.