Skip to main content
Solved

Dynamic date formula

  • December 14, 2022
  • 21 replies
  • 200 views
J
Forum|alt.badge.img+8

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?

Best answer by Andrey_Kovalev

It seems I managed it working.

Calendar formula

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

Date formula

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

Today formula

Today()

 

1 Attachments

    21 replies

    Andrey_Kovalev
    Forum|alt.badge.img+20

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

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

    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 14, 2022

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

    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
    Forum|alt.badge.img+20

    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)
    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 14, 2022

    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
    Forum|alt.badge.img+20

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

    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 14, 2022

    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. 


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

    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 14, 2022

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


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

    Andrey_Kovalev
    Forum|alt.badge.img+20

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


    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")

     

    Andrey_Kovalev
    Forum|alt.badge.img+20

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


    @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?

    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 14, 2022
    @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?


    Ok thanks @Andrey_Kovalev I test with the new formula, the idea is that its different API reading from the tables, it may work with views, I got to test with the api and the system that reads it.

    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 14, 2022

    Ok thanks @Andrey_Kovalev I test with the new formula, the idea is that its different API reading from the tables, it may work with views, I got to test with the api and the system that reads it.


    It seems that the week formula shows the wrong week? there is no week 53 in 2022?

    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 14, 2022

    It seems that the week formula shows the wrong week? there is no week 53 in 2022?


    Found it, you have to tell the formula to start on Mondays so the week formula should be

    WEEKNUM(Date,"Monday")

     

    Andrey_Kovalev
    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 15, 2022

    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")

     


    Hi @Andrey_Kovalev , The formula doesn't work like I want to, maybe I explained it wrong. The 2 weeks calendar should be "rolling", i.e today the 14th will after midnight local time change the date to Wednesday in 2 weeks time, the 28 th. Any ideas would be much appreciated?

    Andrey_Kovalev
    Forum|alt.badge.img+20

    Are you going to keep older records or replace them with new ones? Do you mean the 14th to be replaced with the 28th OR do you mean that after the 14th next comes the 29th in your calendar?

    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 15, 2022

    Are you going to keep older records or replace them with new ones? Do you mean the 14th to be replaced with the 28th OR do you mean that after the 14th next comes the 29th in your calendar?


    HI @Andrey_Kovalev , thank you for your time. There are just 10 records (2 weeks of workdays) , the 14th will be replaced with a new date, just a date change when passing midnight on the right day. As you see in the picture I now use the same table for both odd and even weeks. So the 14th should have changed date to 28th yesterday midnight because its the next upcoming even Wednesday.

    1 Attachments
    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 16, 2022

    HI @Andrey_Kovalev , thank you for your time. There are just 10 records (2 weeks of workdays) , the 14th will be replaced with a new date, just a date change when passing midnight on the right day. As you see in the picture I now use the same table for both odd and even weeks. So the 14th should have changed date to 28th yesterday midnight because its the next upcoming even Wednesday.


    @kuovonne do you have a genius idea up your sleeve?

    Best regards

    Jesper

    Andrey_Kovalev
    Forum|alt.badge.img+20

    I can't imagine how to achieve your goal without automation. It seems I can go through the cycle, but have no idea how to fix Monday as a first record.

    Andrey_Kovalev
    Forum|alt.badge.img+20
    • Andrey_Kovalev
    • Inspiring
    • Answer
    • December 16, 2022

    It seems I managed it working.

    Calendar formula

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

    Date formula

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

    Today formula

    Today()

     

    J
    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 16, 2022

    It seems I managed it working.

    Calendar formula

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

    Date formula

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

    Today formula

    Today()

     


    Hi @Andrey_Kovalev Yeah that might work, I will test it right away!

    J
    Forum|alt.badge.img+8
    • Jesper_Holmstro
    • Author
    • Known Participant
    • December 16, 2022

    Hi @Andrey_Kovalev Yeah that might work, I will test it right away!


    Hi @Andrey_Kovalev it looks great the only thing is that you have change the autonumber to numbers so the second Monday starts on 8. Will be interesting to see how it moves after midnight! Thank you again for your time and solution!

    Best regards

    Jesper

    Andrey_Kovalev
    Forum|alt.badge.img+20

    I've been testing replacing Today value with the formula DATETIME_PARSE('<whatever date you want to test>').

    J
    Terms & ConditionsAccessibility statement