For ANY date(mm/dd/ccyy)…,
I’m looking for (in std “C”) code that correctly(spot on) calculates the Year’s first week(ccyy/1) number with its associated week’s starting date, and also the year’s last week(ccyy/5#) number with its associated week’s starting date; and the returns the number of weeks for that specific year(ccyy of mm/dd/ccyy date). All these calculations must be spot on. Also in the premise, is that the input date is a good date and passed all edits. I have tried two major times before (thinking I had the tiger by he tail), and all results have gone south. This is why I’m asking for help, and I’m attempting it again. These calculations may sound easy, but there are so many rules to follow. December and into January are usually the bugger months in determining the years last week number calculations. Any help, I’d appreciate it. But thanks though.
Can you clarify a little bit what you are looking for? So you want the first week number for a year (which would always be 1). The last week number for a year (which is easy to calculate in a math column using the WEEKNUM function against the December 31st). I don’t think you need to calculate the first number since it’s always 1. You just need to calculate to last number, which is reasonably doable with some math.
You can do this stuff pretty easily in a javascript column, but dates as parameters is a whole other can of worms, so I prefer using the math column.
If I’m understanding what you want correctly, I’ll create you a math formula to calculate the last week number of a year for a given date.
Here’s a preliminary formula to use in a math column. Not sure if this is exactly what you are looking for, but given any date, it will calculate the week number for the last day of the year. That will be the total number of weeks.
WEEKNUM(((Date-DAY(Date)+15)+TRUNC((12-MONTH(Date)+1)/12*365.2424))
-
DAY((Date-DAY(Date)+15)+TRUNC((12-MONTH(Date)+1)/12*365.2424)))
Thanks for responding to my question, a lot don’t respond because I can’t phrase an understandable question. Thanks.
But here goes,
I am of the ole college, and have learned “C” and have done quite well with it.
Yes, given any date(mm/dd/ccyy), I am looking for the following info:
- the first week( wk #1) with the associated starting week date.
- the last week( wk #5?) with the associated starting week date.
this is where things can get a little sticky. - return the total number of weeks within the year(ccyy); either 52 or 53.
That WEEKNUM function that you referenced, I know nothing about.
In doing some research about computing spot on year/week_numbers, there are a lot of rules that you must understand upfront.
Thanks a bunch,
Thad.
I look forward in hearing back from you.
oh, I’m having problems with my email acct.
I assume this would always be 1. Nothing to really calculate here.
Have you tried the formula I gave you in a math column?
Wouldn’t this be the same result as your second question?
Maybe I’m still misunderstanding what you want with points 1 and 2. Are you looking for specific dates or just week numbers? I have a couple of other formulas to get the first and last date of any year. If you’re looking for a Sunday or Monday based on those dates, those formulas would be easy to modify.
I think it might help to see an example of what you want. Show me a given date and what you expect to see as results for points 1, 2, and 3.
Thanks for responding back,
The work is from Sunday to Saturday. NO ISO stuff.
I’m looking for three things, from a subroutine/function: :
The input to this function will be a date; any date; in the form of “mm/dd/ccyy”
Then the function will return three things:
-
Week# 1’s starting date. ← the first week of the year. An output date, in the form of (mm/dd/ccyy)
-
Week# 5#'s starting date. ← the last week of the year. An output date, in the form of (mm/dd/ccyy)
-
return back the number of weeks( 52 or 53) in the year(ccyy). For any given date. (mm/dd/ccyy)
As for the “Have you tried the formula(s) I gave you in a math column?”, I know nothing about this formula(s), please be explain.
Thanks for responding back, I hope all this helps.
Thad.
| Jeff_Hager Glide Expert
July 15 |
- | - |
ascrewball:
the first week( wk #1) with the associated starting week date.
I assume this would always be 1. Nothing to really calculate here.
the last week( wk #5?) with the associated starting week date.
this is where things can get a little sticky.
Have you tried the formula I gave you in a math column?
return the total number of weeks within the year(ccyy); either 52 or 53
Wouldn’t this be the same result as your second question?
Maybe I’m still misunderstanding what you want with points 1 and 2. Are you looking for specific dates or just week numbers? I have a couple of other formulas to get the first and last date of any year. If you’re looking for a Sunday or Monday based on those dates, those formulas would be easy to modify.
I think it might help to see an example of what you want. Show me a given date and what you expect to see as results for points 1, 2, and 3.
So all returned dates should be a Sunday?
That’s why I asked for examples. I want to see a real world example of input and expected outputs. Take today’s date as an input for example. What dates do you expect to see for Week 1 and Week 5#.
So if we take this year for example, Jan 1st is on a Monday. Would you want Jan 1st, or Dec 31st since the 31st would be the Sunday for that week?
Create a new Math column in the data editor. Copy and paste the formula I gave you. Point ‘Date’ to the column that contains your date.
Okay Jeff Hager, of Glide Community.
This math formula you referenced, makes no sense at all.
This ‘WEEKNUM’ routine looks a little cryptic. Care to rewrite that formula into series of lines of code that make perfect sense and with all supporting functions. And can you guarantee it, it will work?
The work is from Sunday to Saturday. NO ISO stuff.
Again,this is what I’m looking for.
I looking for a “C” routine, in code form, that does the following:
A routine has four parameters,
- an input date, any date, in the form of “mm/dd/ccyy”
The input to this function will be a date; any date; in the form of “mm/dd/ccyy”
Then the function will return three output things:
-
Week# 1’s starting date. ← the first week of the year. An output date, in the form of (mm/dd/ccyy)
-
Week# 5#'s starting date. ← the last week of the year. An output date, in the form of (mm/dd/ccyy)
-
return back the number of weeks( 52 or 53) in the year(ccyy). For any given date. (mm/dd/ccyy)
///////////////
You need to be more clear’er. And please, no more cryptic math formulas.
THad
Thanks for responding back,
The work is from Sunday to Saturday. NO ISO stuff.
I’m looking for three things, from a subroutine/function: :
The input to this function will be a date; any date; in the form of “mm/dd/ccyy”
Then the function will return three things:
-
Week# 1’s starting date. ← the first week of the year. An output date, in the form of (mm/dd/ccyy)
-
Week# 5#'s starting date. ← the last week of the year. An output date, in the form of (mm/dd/ccyy)
-
return back the number of weeks( 52 or 53) in the year(ccyy). For any given date. (mm/dd/ccyy)
As for the “Have you tried the formula(s) I gave you in a math column?”, I know nothing about this formula(s), please be explain.
Thanks for responding back, I hope all this helps.
Thad.
July 15 |
- | - |
the first week( wk #1) with the associated starting week date.
I assume this would always be 1. Nothing to really calculate here.
the last week( wk #5?) with the associated starting week date.
this is where things can get a little sticky.
Have you tried the formula I gave you in a math column?
return the total number of weeks within the year(ccyy); either 52 or 53
Wouldn’t this be the same result as your second question?
Maybe I’m still misunderstanding what you want with points 1 and 2. Are you looking for specific dates or just week numbers? I have a couple of other formulas to get the first and last date of any year. If you’re looking for a Sunday or Monday based on those dates, those formulas would be easy to modify.
I think it might help to see an example of what you want. Show me a given date and what you expect to see as results for points 1, 2, and 3.
It’s a math formula. Not a code function. Have you at least tried it in a Math column like I previously suggested? I’m not sure what you want me to rewrite. Math is math. Yeah, it’s cryptic, but it’s proven and date math is a whole other ballgame anyway. There really isn’t another way to write it unless I add some more spaces and carriage returns to pretty it up. I’ve used math formulas extensively for calculating date related values. I’d say I have a pretty good grasp of how it works.
I don’t know why you keep referring to ISO in this respect. Dates are stored in ISO format behind the scenes, but generally they are viewed with their respective friendly formatting based on the user’s region settings. Not sure how your request is relevant in this respect.
Again, I’m asking for real world examples with real dates to help me understand what you are expecting. I’m not going to try to guess what you mean by mm/dd/ccyy (which is a little vague) when trying to determine exactly what you are looking to have for a result based on an actual input date. I still don’t know if week 1 would include a date from the previous year, because you haven’t answered that question yet. That’s a big factor in determining the resulting formula.
None of this needs code. This is all very basic stuff that can be accomplished with simple Math columns.
I’m trying to help you, but you need to provide answers to the specific things I’m asking for and actually try to use the MATH formula I gave you in a Math column. Repeating the same response isn’t getting us anywhere.
If you are looking for C code, you are in the wrong place. Glide functions with either the build-in computed columns, javascript, or Excel formulas. C is not a valid language anywhere in Glide, unless you are trying to do some backend code somewhere outside of Glide. I am a software developer by day, and I do know code, but that’s out of scope for Glide unless you are using a javascript column. Regardless, javascript is not a good solution here for various reasons. The Math column will function much better and more reliably.
If you want help, at least answer my questions and try the formula I’ve given you so far. Don’t just keep repeating the same things over and over. If you are just looking for C code, ChatGPT can spit out a result pretty quickly. Still no idea how you think C code would apply to a Glide app though.
If you are looking for an explanation of the formula, I break it down in the following post. The formula I gave you is a spin off of the core formula I’ve always used. It’s an old post and I’ve tweaked the math over the years to account for leap years and leap centuries. (None of which matter in your case.) This math has been used by many and proven over the past few years. It’s complex to explain, but makes sense once you take the time to understand it.
Okay Jeff Hager, of Glide Community.
I’m sorry for the boldness. I am trying to answer and elaborate things to answer your questions.
To me, This math formula you referenced, does not make any no sense to me.
I know its a math formula template. But somehow, you need to rewrite Your Math Formula into a programmable function to further validate your clams…
It’s like running test trials thru your math formula,and a your programmable function… With output from each
side of each test trial run, with an input date value.
With many test trial runs, you can make your claims and/or stand on your Math Formula to be true.
To me, in some places of your math function, it looks like your subtracting a number# from a date: 12/24/1990. - 17; how can you do that? Do this for me, using your math formula:. and explain this, do a test run with a valid date, and show me the output.
Make each step, from your Math formula, clear…
Run a few example dates thru your math formula to show off the functionality of the formula… While
showing what each step is doing, notate/write_down the output of each step. Use 12/29/2024 as
a starters.
I have never even heard of math column or your math formula before.
The work is from Sunday to Saturday. I’m not truly fawnd of ISO.
The date form of “mm/dd/ccyy” is 12/29/2024
Month(mm) is ‘12’, Day(dd) is ‘29’, and CenturyYear(ccyy) is ‘2024’.
Again, This is the code I’m looking for.
I looking for a “C” routine, in code form, that does the following:
A routine has four parameters,
- an input date, any date, in the form of “mm/dd/ccyy”
The input to this function will be a date; any date; in the form of “mm/dd/ccyy”
Then the function will return three output things:
-
Week# 1’s starting date. ← the first week of the year. An output date, in the form of (mm/dd/ccyy)
-
Week# 5#'s starting date. ← the last week of the year. An output date, in the form of (mm/dd/ccyy)
-
return back the number of weeks( 52 or 53) in the year(ccyy). For any given date. (mm/dd/ccyy)
///////////////
But yet, the Year/week_number must accurate, correct, and spot on when called upon.
As for CHATGPT, I’m learning very fast, not to trust CHATGPT. It can turn out garbage real easy and fast - ever thou it(CHATGPT) thinks it’s right.
week 1 is the first week of the calendar year.
e.g; the date 12/29/2024 is Year/Week_Number 2025/1
week 52 for 12/28/2024 is 2024/52.
THad
I hope I have explained things out. But now, you must make your stand with validated proof and evidence that you can make a stand on.
Thanks.
July 17 |
- | - |
This ‘WEEKNUM’ routine looks a little cryptic. Care to rewrite that formula into series of lines of code that make perfect sense and with all supporting functions. And can you guarantee it, it will work?
It’s a math formula. Not a code function. Have you at least tried it in a Math column like I previously suggested? I’m not sure what you want me to rewrite. Math is math. Yeah, it’s cryptic, but it’s proven and date math is a whole other ballgame anyway. There really isn’t another way to write it unless I add some more spaces and carriage returns to pretty it up. I’ve used math formulas extensively for calculating date related values. I’d say I have a pretty good grasp of how it works.
The work is from Sunday to Saturday. NO ISO stuff.
I don’t know why you keep referring to ISO in this respect. Dates are stored in ISO format behind the scenes, but generally they are viewed with their respective friendly formatting based on the user’s region settings. Not sure how your request is relevant in this respect.
Again,this is what I’m looking for.
Again, I’m asking for real world examples with real dates to help me understand what you are expecting. I’m not going to try to guess what you mean by mm/dd/ccyy (which is a little vague) when trying to determine exactly what you are looking to have for a result based on an actual input date. I still don’t know if week 1 would include a date from the previous year, because you haven’t answered that question yet. That’s a big factor in determining the resulting formula.
You need to be more clear’er. And please, no more cryptic math formulas.
None of this needs code. This is all very basic stuff that can be accomplished with simple Math columns.
I’m trying to help you, but you need to provide answers to the specific things I’m asking for and actually try to use the MATH formula I gave you in a Math column. Repeating the same response isn’t getting us anywhere.
If you are looking for C code, you are in the wrong place. Glide functions with either the build-in computed columns, javascript, or Excel formulas. C is not a valid language anywhere in Glide, unless you are trying to do some backend code somewhere outside of Glide. I am a software developer by day, and I do know code, but that’s out of scope for Glide unless you are using a javascript column. Regardless, javascript is not a good solution here for various reasons. The Math column will function much better and more reliably.
If you want help, at least answer my questions and try the formula I’ve given you so far. Don’t just keep repeating the same things over and over. If you are just looking for C code, ChatGPT can spit out a result pretty quickly. Still no idea how you think C code would apply to a Glide app though.
Why? The Math column doesn’t do programmable functions. It does math with numbers and dates. That’s it. Writing it like code with functions, comments, parameters, etc. will no longer make it a valid math formula and it will fail with an error. You don’t write code into a basic calculator. You just type in the formula to do the math.
It’s very basic math. Calculate to the last day of the year given any input date (Dec 31st YYYY), and retrieve the Week Number from that date. Pretty simple actually. It’s not up to me to provide several test results. I’ve tested it. It works. You are welcome to test it yourself if you don’t trust it. I did the work to figure it out and I’m providing it to you for free. My time…your benefit.
Works great.
I posted a link to a thread that explains the general concept. Read it and understand it.
If you don’t know how to use the math column, I suggest you take some time to read through the docs and maybe take some time going through the tutorials in the Glide University. I’m not sure you understand how Glide works.
I’m not paid to do this. I’m taking my personal time to provide free help in the forum. I don’t think I have to prove anything with notations and documentation. Take it or leave it. It’s free advice
This is one of the strangest threads I have ever read!
@ascrewball hope you get your solution soon. @Jeff_Hager has helped countless of people on this forum over the years and my word he knows his stuff!
Thank you @Andrew_Davies. I think I’m done with this thread. If the basic formula for the week number is not trusted, he is not going to like the ones to calculate the beginning and ending dates as they are going to be a lot more complex.
Just adding that I see no point in providing help with C coding as it has no application in a Glide app.
Just want to note that the screenshot in my previous post was not using the same formula that I posted earlier. Looks like it reverted to a different formula because I didn’t save it previously. Plus I had my device configured for European dates, so the combination of one or both of those factors led to incorrect results. The original formula still stands as I had initially understood the request, before I was given sample data to help clarify the request and the expected results.
This screenshot below shows various dates, the calculated weeks based on my original formula (which is valid depending on how you interpret a week), and the calculated weeks based on the user’s clarified request according to the sample data that was provided.
The modified math formula to calculate the weeks in the last column, according to the user’s request, is as follows:
WEEKNUM(((Date-DAY(Date)+15)+TRUNC((12-MONTH(Date)+1)/12*365.2424))
-
DAY((Date-DAY(Date)+15)+TRUNC((12-MONTH(Date)+1)/12*365.2424))
-
CEILING(MOD(WEEKDAY(((Date-DAY(Date)+15)+TRUNC((12-MONTH(Date)+1)/12*365.2424))
-
DAY((Date-DAY(Date)+15)+TRUNC((12-MONTH(Date)+1)/12*365.2424))),7)
/7)*7
-
WEEKDAY(((Date-DAY(Date)+15)+TRUNC((12-MONTH(Date)+1)/12*365.2424))
-
DAY((Date-DAY(Date)+15)+TRUNC((12-MONTH(Date)+1)/12*365.2424)))+1)
No, I did not do substantial testing on this, but I did throw a few test cases to prove the concept.
Calculating the actual beginning and ending dates at this point is trivial and just needs a few minor modifications to this base formula. At this point, I think this is about as far as I am willing to take it. Just wanted to clear up the error in my original screenshot and confirm that the request was still attainable.
Ok boys, If you say your math formula works, then prove it. Make a list of dates to run thru the math_formula, and show me the output results that comes up from each test run( on paper and in a programming language)… By running these test runs, it will prove to us and others that your formula actually works. So if your test run data comes out positive and you will be vindicated and your word will be much stronger. Just your word and actual test run data/facts are two different things. So if you make a statement that something is correct, you are going to have to prove it with cold hard facts; cause word alone doesn’t cut it.
In joining this site, I really thought you could help me out, but I found out otherwise. So if you give up now, you have just copped out and lost, and having not done any homework. I guess I lost the help, but at least I didn’t cop out and make excuses. SO boys, prove to me. Are you men enough to accept this challange Or are you both quitters.
BYE
@Jeff_Hager - I am beginning to think ascrewball is a bot or something?
Back to the topic - I had a similar task a few days ago. I’m moving a large budget for a project I manage in Airtable to Glide - just as a test for now. (If anyone is interested or a Doctor Who fan - this is the project)
I wanted to create a monthly spend report - so needed the first and last calendar days of a given month (so I can do a query on my transactions table based on a range of dates)
I used two JavaScript code columns to get the two dates from a single input. (Ok - ChatGPT helped me!). Seems to work ok , though I need to put it through some proper testing. Using Glide’s AI Prompt component - I could very very very easily create an input field that allowed the user to just select a month and year (excluding the day) so that could be used in the calc. “Get a date from the user - but only month and year” - as easy as that
The JavaScript code returned the date as a text string, but Glide’s “Text to Date” column converted it ok.
I would rather do it using a Math(s) column, but Glide’s Math(s) column doesn’t allow for functions such as EOMONTH etc. I tried Glide’s "Excel Formula’ in the experimental section - but didn’t get any joy there either.
Not sure if it will, but hope that helps someone.
@Andrew_Davies so you want to have a couple of choice components to choose from? One with a list of Years, and one with a list of Months?
Don’t want to kidnap the thread but sort of.
End goal is to have a query column returning all rows from a transaction table that fall within a calendar month - ie January 2024. Ideally the user can select a month of a year and get those transactions.
Was trying to do it with a single date (ie 12th May 1980) and two computed columns with 1st May 1980 and 31st May 1980 to use as the date range.
Suddenly feel like there’s an easier way?! Maybe better to have the user select and month and year separately - as you suggest - with two choice components.