Help

Dynamic date formula

Solved
Jump to Solution
6160 21
cancel
Showing results for 
Search instead for 
Did you mean: 
Jesper_Holmstro
6 - Interface Innovator
6 - Interface Innovator

Hey guys

I have a brain drain problem with how I should set up a dynamic date formula, I have 2 tables in a base, one for odd weeks and one for even weeks. They both just contain 5 records, one for each workday. I have separate fields where the current date is shown, current weekday, current week number and if the current week is odd or even (it shows 0 for even and 1 for even). Now to the tricky part.

I want to show in a field in the even table (week 50), lets say today Wednesday the 14th of dec. I want todays date to be visible in that field, on Tuesday the 13th should be visible and on tomorrow the 15th and so on. And I also want if the dates in the odd table shows next weeks date, and then when it becomes an odd week the even table updates the dates to the week after the odd. Any of you geniuses have a solution in mind?

1 Solution

Accepted Solutions
Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

It seems I managed it working.

Andrey_Kovalev_0-1671183673796.png

Calendar formula

IF(Today <= Date, Date, DATEADD(Date, 14, "day"))

Date formula

DATEADD(Today,Autonumber-WEEKDAY(Today),"day")

Today formula

Today()

 

See Solution in Thread

21 Replies 21
Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

@Jesper_Holmstro , hi there! Is it what you expected to get?

Andrey_Kovalev_0-1671031372168.png

I will explain later if it is in the right direction.

Hi @Andrey_Kovalev yes that looks as it could do the trick?

Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

So, Autonumber field is used to increment the date.

Date formula is

DATEADD(TODAY(),Autonumber,"day")

Name formula is

DATETIME_FORMAT(Date, "dddd")

Week formula is

WEEKNUM(Date)

Odd Even formula is

MOD(Week,2)

Hi @Andrey_Kovalev sorry but that didn't do the trick. Since there are 2 tables, one for odd weeks and one for even weeks they get the same date hence the auotnumber is the same 1-5. So I don't really know how to get forward

The formulas for the name, week and Odd even works fine. 

Andrey_Kovalev
8 - Airtable Astronomer
8 - Airtable Astronomer

What is the reason of having two separate tables for odd and even weeks?

I sort of need the autonumber to change every day so the dates are up to date?

Its 2 different sides in the user interface made for clients.

I made a mistake in Date formula. Instead of TODAY() a fixed date should be used, otherwise the date would shift every new day

DATEADD(DATETIME_PARSE("2022-12-14"),Autonumber - 1,"day")

 


@Jesper_Holmstro wrote:

Its 2 different sides in the user interface made for clients.


Do not get the idea. Creating two different views on the same table would not work?