Has anyone faced the same issue?
The highlighted row shows that the admin is within given period of time. Therefore, it should be 1. It has been working well until few minutes. I didn’t play around with it at all!
So it has been working well since I advised you to do this in the last thread?
1 Like
Yes. I followed your steps along with Jeff’s idea and it worked well. By accident I wanted to run a test and found it like that.
Try checking if the date associated with your Start and End times are “today”. I’m guessing they’re actually those times, but from past dates.
The best thing to do is turn the times into their own values and do less than, or greater than calcs. This will ignore the value of today when comparing times only (as dates, it will look at the full date value, not just the time… Even though the formatting only shows the time).
1 Like
This is the “Now” in time only.
when I turned it to “Date and Time” it shows me today’s date.
This is the Start Time in “Time” only. I entered it manually.
When I switched Start Time to “Date and Time” it shows me the past date.
you are right but how come it was working well for more than 3 days I believe since I set it up!
@kyleheney what do you mean by turning them into their own values? you mean number columns instead of time?
It was working because “Now” matched the dates that start and end are using. Now that it’s 3 days later, NOW has updated itself to today’s date, but your start and end times are static dates and are still 3 days ago.
How to fix it then? Any clue? How to make all of them, Now, Start and End time dynamic?
What I do is have a sheet with all my time choices in 15 minute intervals (one column). These are in a time format.
In the next column, create a Template that just uses the Time column. This will lock in the first column as text.
In the next column, I have number values for each time (so 5:00 AM in column B becomes 5.00 in column C; 5:15AM corresponds to 5.25AM, 5:30 to 5.50 and so on).
Next, create two template columns in your log sheet to lock in the start and end times as text. In fact, your choice components where you select the start and end times could just use the text values of times and don’t need to be date columns.
Next, create a single relation from your log sheet to the choice sheet with all these time values and numbers. So relate the start time choice to the choices sheet and do a lookup to get the number value that corresponds to the time choice.
Once you’ve done this for the start and end, you should be able to do calcs on those numbers (then convert the result back to a time value, if needed).
You’ll also need to lock in the Now value using a template, if you only need the time and not the date.
3 Likes
Your method seems interesting but confusing at the same time.
I have two sheets:
- User Profile: where I have user email address, timestamp, start time and end time (time format)
- Check in: Where I have User’s email address and timestamp.
Based on your method, in user profile on glide, I created two templates and two number columns (total of 4). Next, In check in on glide, I related the user email with the email address of the user in profile sheet to grab the number values. I also locked “Now Time” using template in check in tab. When I tried to run math calculations, it didn’t work.
I don’t know if you got my point. Would you mind if I share my app with you in private? so you can run quick review to find the mistake? it is very simple! I just need you to focus on data editor then I can set submission forms etc on the app using the data editor!
It worked after watching @Robert_Petitto video on YouTube. It is very much similar to your method @kyleheney but I couldn’t get the point of “own values”. Thank you brother for always helping me with ideas 
1 Like