Use these blocks to read/write values in the sheet
The result column has this formula =K2 - J2
The start date column and the end date column need to be formatted as DATES for the formula to work.
1 Like
Forgive me but I am slightly confused. I understand that Google sheets can read the dates and determine their difference. However, how would we put the dates there? Wonāt there just be rows and rows of dates that would be inserted every time the screen opens?
1 Like
If you see the block I shared, it says update value. You need this just to get the difference of the selected assignment so you will have one single row in this sheet.
Whenever you want to check the difference between any dats, you will update column start date and column end date with your two dates and you will read the difference in days from the third column.
1 Like
@muneer alright thank you, I will try implementing this. I will let you know if I am successful or not.
1 Like
If you donāt like the Google sheet approach then let me know to adjust the procedures we built together for the date functions and make if work the expected way.
1 Like
@muneer Personally, I would prefer the first approach if you do not mind. I would like to keep things as local as possible in terms of data manipulation which why I have for the most part not used external data sources. The Google Sheets are always an option however. But trying to adjust the previous code would be more preferable for me if you do not mind. Thanks.
1 Like
@muneer I was thinking for simplicity, I could change the date format to MM/DD/YYYY. Would that help in any way with comparing dates?
1 Like
I redesigned the function we did together.
After getting the date as YYYYMMDD we need to do the following
Supporting variables and functions
Main function
This is how you call it
Sample result
This gives the number of days since beginning of the year in addition to the year value. It will computer the number of days correctly.
You can check using this calendar
https://www.calendar-365.com/day-numbers/2021.html
1 Like
Thank you so much @muneer , there is a couple of things I have noticed however.
In these supporting variables app variable monthCounter as well as app variable yearCounter are not used in your given functions. Are they still necessary? Whats more is that there are the variables inputYear , inputMonth and inputDay which I do not see initialised. Are these the dates components of each date that has been parsed from the assignment?
I also donāt currently see an option to pass variables into a function as you have done here. Is this possibly a discrepancy between StP and DaD? If so, how can I get around this?
Thanks!
1 Like
Edit:
I have found out how to pass in variables:
Thatās ok, you donāt need to use them. Iām using them for another task which converting seconds since 1970 to a proper date. This is not required for your function.
When you add a function to your code, you can use the gear icon in the function block to add parameters. Those will show in the variables drawer but will be differentiated by having the name only without the proceeding app variable or similar.
1 Like
Thanks @muneer . To confirm, thisMonth = current month correct?
Also, where is thisMonth being initialised?
1 Like
No sir, it isnāt.
thisMonth is a running counter for the loop and not to be used outside of the loop block. It requires no initialization.
2 Likes
@muneer I see but if it needs no initialisation, where is the variable made? Is it a parameter to another function because I can see it in any other function in the code you supplied?
Thanks!
1 Like
When you add a looping block such as the count block I used in this example, you will have a default variable i. You can click on it and notice the option before last in the pop-up menu Rename variableā¦
click on the option and you should see a dialog box to input the new variable name. Enter your new variable to replace the default i.
Click OK and now you should see your new variable in the looping block.
Hope this clarifies the issue.
1 Like
@muneer thanks. If the variable is simply a running counter, what is the purpose of naming it āthisMonthā?
1 Like
-
Having it as i means nothing when you come back to the code again after a while and you loop is not a 2 lines block. Specially if the loop is inside a function with other variables.
-
Avoid confusion when you have a loop inside another loop, you want to n
know which i are inserting in your blocks, the one belonging to the inner loop or the one to the outer loop. -
Coding standard that you need to keep to instead of every app has a different way of coding.
Hope this suffices.
2 Likes
Sorry but what does āiā have to do with months? I just asking since I want to fully understand this procedure. I completely understand that it common practice to rename variables to differentiate between loops I am just confused as to what this counting variable has to do with months. Thanks for the explanations @muneer .
1 Like
I needed to total the number of days in the year starting from January to the month entered by the user. I started with a list of number of days in each month and then looped through the number of months till the inputMonth
This is why I renamed the default i to thisMonth.
You can name it anything you would like or set it back to the default. The name will change nothing as long as you add up every number of days in the month from that start of the year till the one just prior to the month of the date being processed.
To get a correct number of days, we need to accumulate the number of days in each month from the start of the year until the month prior to the date we are processing and then add the number of days from the date we are processing.
In my example, I selected 24th October 2021 therefore I need to add up the total number of days from 1st January 2021 until the selected date. I also add the year value so for the example given the number should be 2318.
If you use the same process with any other near date and compare them together you should get the correct number of days which is what you wanted in the first place.
1 Like
Thanks @muneer , this clears things up a lot. I will try and implement the procedures and will report back my results.
1 Like