🕒 System/GMT Time - Solved!

Don’t trust anything that Jeff guy says:

I’m looking at this closer. I think I get what’s happening now. I think it only affects you for the last 8 hours of the last day of the month. I won’t see it until the first 5 hours of October 1st since I’m on the other side of GMT.

The confusing thing here that when you add GMT to the date/time in a template column, then glide assumes the date/time as if it was actually GMT. So when you show that GMT time or pick out the parts of the GMT date, then the equivalent of Singapore date/time is 8 hours after the GMT date/time, which was pushing the date to Oct 1st. My logic to add or subtract 24 is applying somewhat correctly, but in the wrong direction. It’s supposed to offset a difference of -16 by adding 24, but instead it subtracts 24, which gives you an offset of -40.

I quickly tried a change which fixes it on your end of the world, but not on mine. I’ll have to rethink the logic a little bit and hopefully update the formula. Basically I’m trying to use the below part of the formula to get a -1, 0, or +1 if the month changes, then multiply that value by 24, so the hour difference can be adjusted properly if it crosses different days. I have some ideas to fix it, but I’ll have to do some good testing. I’ll let you know when I have it fixed.

(24 
* 
(ABS(day(GMT)-day(LDT))
/
(day(GMT)-day(LDT)))
)
1 Like

yup!

That explains why it worked for my boy in Melbourne last night when I asked him to test - because it had already ticked over into the new month down under. It also explains why it didn’t work for our friend in Vietnam on his Windows machine - because it was still in that 8 hour “black hole” period in Vietnam. So the OS theory was just a red herring :laughing:

At least we know what’s going on now, and the good news is we have a whole month (almost) to figure out how to fix it! :rofl:

Edit: Just quickly tested for a few days other than the first/last day of the month, to make sure…

2 Likes

really? nobody mentioning 12h off GS vs Glide? and different 0 dates? and why going this circus when we can relate to absolute time numbers?
time Apps by StructureArt:

I have no idea what you mean by that.

I also have no idea what you mean by that.

The reason I am persisting with this is because it’s a pure Glide solution that requires no Spreadsheet formulas, and no experimental columns, and nothing else external to Glide other than the users own device. If you can show me an alternative solution that meets those criteria, then I’m all ears :slight_smile:

2 Likes

try to put “0” on Date cell format in GS and Glide and see what happens… Glide is the base origin GS app creator… so … let’s stick to GS formats
@Darren_Murphy by absolute numbers I mean time duration, related to TIME NOW on active App… and I just post 2 apps that have this problem solved

@Darren_Murphy Grrr, this would be so much easier if glide would recognize a template date as a date for date math. They will let me pick out parts of a template date, such as Day and Hour, but it won’t work for simple date math. Not sure why.

But I did figure it out and I think I have it fixed. I tested with both my timezone of GMT-5 and your timezone of GMT+8. I also tested within a couple hours of the end of the day on Sept 30th and the beginning of the day on Oct 1st. I also tested a random day in the middle of the month. In all cases it showed me the correct offset and correct GMT time regardless of user timezone.

BEFORE:
image

AFTER:
image

Here is the full replacement formula:

(HOUR(gmt)-HOUR(ldt))

+

(24 * (
ABS((MONTH(gmt)*100+DAY(gmt))
   -(MONTH(ldt)*100+DAY(ldt)))
/
   ((MONTH(gmt)*100+DAY(gmt))
   -(MONTH(ldt)*100+DAY(ldt)))
))

+

(MINUTE(gmt)-MINUTE(ldt))/60

Got it fixed with a whole month to spare. :wink:

I will update the screenshot in the original post momentarily.

2 Likes

TIME is a var that relates to a user position in space and speed… that’s why I will suggest using duration… and recalculate time in USC for users… and have anchor point in GS for App developer

Nice!

But, but, but… it breaks on Dec 31 :rofl: :rofl:

At first I thought that might have been a leap year thing, but it seems to be all years (see Dec 31, 2023)

PS. But you did mange to extend the deadline by a couple of months :rofl:

1 Like

Come on man, you’re killing me. Hehe, I know how to fix that now. Give me a sec.

1 Like

guys… have fun cracking time formats… like a said … make a duration as absolute… and add to time… my apps are unbreakable for customers around the world! and they always come for more.

So…don’t change your system to next year…it must expire most website certificates, so everything started to crash on me.

Can you try this version quick and let me know if it looks good? If so, I’ll update my screenshots…again.

(HOUR(gmt)-HOUR(ldt))

+

(24 * (
ABS((YEAR(gmt)*10000+MONTH(gmt)*100+DAY(gmt))
   -(YEAR(ldt)*10000+MONTH(ldt)*100+DAY(ldt)))
/
   ((YEAR(gmt)*10000+MONTH(gmt)*100+DAY(gmt))
   -(YEAR(ldt)*10000+MONTH(ldt)*100+DAY(ldt)))
))

+

(MINUTE(gmt)-MINUTE(ldt))/60
2 Likes

No dice, I’m afraid…

Sorry, I had gmt in the ldt spots. I updated the above formula.

Wonderful!!

I never doubted you for a second :wink:

2 Likes

:crazy_face: Finally!

1 Like

Yes. You have my permission to get some rest now :stuck_out_tongue_closed_eyes:

3 Likes

Give me something more challenging next time. :wink:

4 Likes

It’s still buggy https://embarrassed-shoe-5921.glideapp.io/

It’s working for me?

I access by computer