More manipulation data functions

Ideas & Features
1

I’m new to Fibery and something I find very limiting is lack of ability to manipulate dates. For example, suppose I have a database named “Tests” where I store some activities that have different periodicity. If I want to add a button to postpone undone tests to next period, calculate new date based on entity periodicity (for example monthly, quarterly, weekly, daily etc) is really tricky. Lots of nested IFs are needed and dealing with 29 february is impossible.

Having functions that adds x months or x weeks would be very useful.

2

Adding x weeks is pretty easy:

NewDate = OldDate + Days(7 * x)
(pseudo code)

Adding months is a bit trickier, but it’s not impossible.
However, some trickiness is unavoidable, given you need to determine what the desired behaviour is in the corner cases, like 29th Feb as you mentioned.

For example, what is Jan 31st + 1 month?

3

Thank you for your always superfast reply. I agree that some operations can be obtained and “x weeks” would be just a shortcut of Days(x). But if you have an entity that have a “Periodicity” field and need to deal with all periodicities in one single formula to assign the new value lots of IFs are needed just to implement the “jump” of year in case you add 1, 2, 3, 4 months and so on.
Just having something like Months(x) the same as Days(x) would help a lot in this case, avoiding lots of IFs to manage all cases and calculate the “happy new year”.

Jan 31st + 1 month is exactly the “end of month” concept I was thinking about. In some cases this should be Feb 28th or Feb 29th leap years. Of course this is a borderline case but the same concept applies when next month is 30 days instead of 31.

I understand that this raises a lot of questions about how to define the behaviour of a possible Months function.

4

There’s potentially even more to consider than just what should happen if there are fewer days in the following month. Like what should happen with repeated increments:
March 31st + 1 month → April 30th
April 30th + 1 month → May 30th ? or should it ‘rebound’ back to May 31st, given that it is conceptually ‘the last day of the month’?

Eventually I reached the conclusion that the easiest thing to do may just be to round down all calculated dates to the 28th of the month. For the first occurrence, the calculated value may be ‘wrong’, but after that, it all settles down.

Alternatively, why not just add 30 days, and acknowledge that the day of the month will gradually creep down.

Or perhaps it makes sense to pick a working day, in which case, add 30 days, and then move to the next available working day.
This will quickly settle down to a pattern of Monday, Wednesday, Friday, Monday, Wednesday, Friday, … and after 12 monthly increments, you will have added 364 days. For a lot of people, this is good enough.

TLDR: there are some nice solutions that don’t require too many if…thens.

Otherwise, the nicest formula is something like this:

Date(
	If((Month([Date value]) + [Month increment]) > 12,
		Year([Date value]) + 1,
		Year([Date value])),
	If((Month([Date value]) + [Month increment]) > 12,
		(Month([Date value]) + [Month increment]) - 12,
		Month([Date value]) + [Month increment]),
	Least(
		Day([Date value]),
		Day(Date(
			If((Month([Date value]) + [Month increment]) > 11,
				Year([Date value]) + 1,
				Year([Date value])),
			If((Month([Date value]) + [Month increment]) > 11,
				(Month([Date value]) + [Month increment]) - 11,
				(Month([Date value]) + [Month increment]) + 1),
			1)
			- Days(1)
		)
	)
)

I hope you can figure out what it’s doing :slight_smile:

1 Like
5

add the current months number of days - the difference between the current day of month and next months max number of days

its still 4 ifs, but you could reduce it to 2 ifs with a table of Months

1 Like
6

Nice one @James
Wonder how it would scale if you want to add X months (not just add one)?

7

Thank you @Chr1sG and @James for your replies.

Sorry for my late reply but I was very busy these days. In the end I used a date range field that implicitly defines the periodicity of the test/task. So I removed any other fields about periodicity and leaved only one field.
The “Postpone” button can be implemented calculating the new date range with the following formula:

DateRange(
    [Step 1 Ordini].Periodo.End(false),
    [Step 1 Ordini].Periodo.End(false) + Days(ToDays([Step 1 Ordini].Periodo.End() - [Step 1 Ordini].Periodo.Start())))

This method have no problems with year change and even if it still has some problems with leap years and may loose days in some circumstances, it’s acceptable for the use case and the formula is kept really simple.

1 Like