Calculated Field for Days Count

[yewasa]

Currently there seems to be no way to create a calculated field to count number of days using 2 dates field type in a form. Is there a way to do this that I couldn’t figure out? If not, will you be making this possible in upcoming updates? Would be very helpful in certain forms that I use, please! Thanks!

[Brendan]

Hello Yewasa,

Yes, you can do this. The result of subtracting one date from another is the number of seconds between those two dates. You just need to apply a bit of math to convert the seconds into days.

So try this formula:

[End Date] – [Start Date] / 60 / 60 / 24

That should get you what you want.

Thanks!

Brendan

[yewasa]

Hey Brendan,

Thanks for your prompt response! You’re right, by converting seconds elapsed between the dates into days using the following formula works.

([End Date]-[Start Date])/60/60/24

It is still an issue to get the number of days passed if [End Date] is always today (which is not fixed). I tried using a date type [Today] & setting the Field Options to “Set Current Date” on as the end date, but the form does not auto refresh to give you the current date when looked up. So I have to manually press the [Today] date field to set it to the current date (Today). Any fix for this issue. Thanks!

[Brendan]

Hi Yewasa,

Oops. I forgot the parentheses in my formula. Glad you caught that.

So instead of [End Date], replace that with $now. If you’re on the iPhone or iPad, there’s a green clock button which inserts that for you. If you’re on Mac, then just type in $now into your formula.

For example:

($now – [Start Date]) / 60 / 60 / 24

That will dynamically insert the current date into the field.

Thanks!

Brendan

[yewasa]

Hi Brendan,

Knowing $now for current date/time (dynamic) resolves all my issues working on creating any calculated fields for counting number of days.

I’m really enjoying using Tap Forms, have been using it for a long time on my iOS devices and recently started using Mac OS (am Windows user by default), a MacBook Air for personal use, and Tap Forms was among the first Mac Apps I have purchased. Tap Forms In all aspects, aesthetically as well as functionality, is a solid database software, it especially shines on iPhone with its limited real-estate. Looking forward to seeing many more useful features in the upcoming updates from you.

Thanks again, really appreciate your top-notch support!

[Brendan]

Thanks Yewasa! I’m thrilled you’re enjoying Tap Forms.

Just preparing my iOS 7 update to submit to Apple. Just a few more tweaks here and there.

Thanks!

Brendan

[yewasa]

With the latest Tap Forms iOS update (both iPhone & iPad versions), the records of any forms with calculated days field (s) do not open when taped on in the list view. Is this a bug?
BTW, really loving the new look matching iOS 7, and other additional features added. Thanks!

[Brendan]

Are you sure that’s not the problem with a checkmark field as the first field? There’s a bug with that which I’ve just fixed.

Can you email me your form template so I can investigate?

Thanks,

Brendan

[yewasa]

Yes. I do have checkmark field as the first field on both the forms having this issue. I’ll await for the update with the fix, hope it will be up for download soon. Thanks!

[Ulyee F]

Thanks for the above. Am in the process of converting some Bento dbs over for iPad and ran into a few things.

They had a Calc result Type known as duration; it seemed to mostly prettify and chunk the date.

The Db i am stuck on is trading options, and had a Calc between the sold and bought dates, ending up as say 7 weeks five days.

Now, on a 1,200 record db i csv imported, I had to change the dates from text to date and also seem to have to go record by record touching the date field to get the Calc to work.

Is there a way to force a db wide recalc on a date Calc?

[Ulyee F]

Fixed it! Just go into the formula, tap the save button top right, and it recalcs

[Ulyee F]

Still running into an oddity; it seems like a leap year thing not accounted for due to Feb being in the date range.

[Ulyee F]

The problem with the formula

https://swizec.com/blog/a-day-is-not-606024-seconds-long/swizec/6755

[Brendan]

Hi Ulyee,

There’s also a refresh button that’ll recompute all the Calculation fields in your formulas. It’s at the bottom of the records list view. So you don’t have to edit the formula to cause that to happen.

What do you have for your formula? All dates and times in Tap Forms are represented by the number of seconds that have elapsed since January 1, 1970 00:00:00 GMT. This is known as the Unix Epoch date. Maybe if you send me your form template I can look at your formula.

[Ulyee F]

([Sold]-[Bought])/60/60/24

The problem is stated in the swizec link.

It is not huge though, and if i turn decimals off it should round up.

[Author]

Posts