How to connect an hour with a date?

Ask for Help
1

Hello all,

I hope this is a really simple question but it might not be that simple.

I have a GYM app with different classes. Each one with a static initial and final hour that repeats weekly.

I have achieve a minimal formula to know when to add 7 days or not. So I have the real-final-date in a column.

So, what I need is to connect that real-final-hour with the static class hour. But I haven’t achieve it yet. I have used the “Now” date to do the minimal formula so the real-final-date hour is the Now hour. But I don’t need that hour to be shown on the calendar. I need the static initial and final hour of the class.

Is there any way to achieve this?

THANK YOU!

2

Let me rephrase it to see if I get your idea, let’s say you have 7 rows that each have their own weekday (e.g: Monday, Tuesday) and then their pure starting and ending time (e.g: 7AM - 9AM).

So when one comes in and register, you want to have dynamic starting and ending time based on whatever date it falls to next week?

Let’s say Tuesday 7AM - 9AM this week would be converted to 15 June 2021 07:00 - 15 June 2021 09:00?

3

Yes, that’s the idea. But when Tuesdays class of 15 June 2021 9:00 has been passed and today is Wednesday 16 June, the app must show Tuesdays class of 22 June 2021 9:00.

What I mean is, how I can connect a static hour (in a separate column) with a dynamic date (that even if you put only show date has a default hour different from the static hour I need)?

1 Like
4

I’d have to check how dynamic it is again, but date columns are actually pretty powerful. If you write the text value of ‘Tuesday at 9:00am’ or ‘Next Tuesday at 9:00am’, it will magically figure out the date. The part I’m not completely clear on is if it will dynamically update the date after each date has passed, or if it becomes static.

Some reference:

@robert states it’s dynamic but maybe found a workaround:

Just some quick testing, when writing text in a date column and then changing it to a text column, it seems to hold it as a static date without time, but somehow retains the time. When writing text in a text column and then changing it to a date column, it seems to hold the text which converts dynamically to the correct date. Again, I’m not completely sure what the behavior will be, but that might be one way to have dynamically changing dates.

5

Hi Jeff, I dived a bit into this with some Math Columns. @Eric_Ruiz_Molero I hope this helps.

image
image1718×728 78.9 KB

So I had a set up of date of week (in text), then start time and end time in numerical.

image
image1786×1254 206 KB

The magic is mostly here:

 WEEKDAY(F)-WEEKDAY(N)+7-CEILING(LOG(WEEKDAY(F)/WEEKDAY(N)))*7

WEEKDAY(F)-WEEKDAY(N)+7 brings me to the corresponding weekday of next week.

Then WEEKDAY(F)/WEEKDAY(N) returns something less than 1 for days that have already passed (for example today’s Tuesday, then Sunday and Monday have passed) and more than 1 for days that have not already passed (Wednesday - Saturday).

LOG puts it in a -1 to 1 scale. Anything less than 1 here returns something less than 0, else it’s more than 0. I convert it to 0 and 1 with CEILING.

-CEILING(LOG(WEEKDAY(F)/WEEKDAY(N)))*7

returns -7 when the day has not already passed (so it’s for this week) and returns 0 when it needs to be next week.

The other 2 columns is just converting the time to the right offset.

image
image2262×1262 256 KB 

N+O-HOUR(N)/24-MINUTE(N)/1440-SECOND(N)/86400+S/24

Now + Offset minus everything related to hour, minutes and seconds for “now”, then add the corresponding starting hours.

Same setup for the ending time.

As this is another date-time related thing I hope you find this useful as well @Robert_Petitto @Darren_Murphy @Lucas_Pires.

Only thing I haven’t done is if it falls to the current day, then it should move to next week only when “now” is past the start time, but I think it’s a bit complex.

8 Likes
6

Amazing.

3 Likes
7

5 Likes
8

No words @ThinhDinh

Thank you so much!

1 Like