Date fields in ToDo Form Problems

[tonyt]

Hi

I am afraid I am NO good when it comes to formulas and functions. I just use TF fro flat field record keeing. However I am trying to create a ToDo form. Here is what I want to achieve – thanks to Leo for his help so far but I’m stumped!

A form that has the following fields:

1.Todays date – this must autofill if possible on creation of a new record and update automatically if the form is opened on a later date.
2.Date created: This I CAN do from a simple date field and calendar pop-up – unless there is a way to have the current days date inserted but then NOT be updated when the form is opened on a subsequent day.
3.Date Due: A simple date field and calendar popup that doesn’t get updated.
4.Pending / Overdue: This is where I really am getting stuck. I want this field to use the ‘Date Due’ field and the ‘Todays Date’ field ( which changes every day )to calculate the number of days left before the event is due (+figure?) or the number of days past the due date (-figure?)

EG
Todays Date. Date Due. +/-
3 March 17. 5 March 17 +2
7 March 17. 5 March 17. -2

I hope this is clear and someone can help me. My head is spinning!

Cheers

Tonyt

[Brendan]

1. You can enable the Set Current Date checkbox on any Date, Time, or Date & Time field. When a new record is created, the current date will be stored. But it won’t modify automatically later on when the form is opened. But you can modify it yourself.

2. Add the Date Created field type to your form. It’s at the bottom of the list of fields.

3. Just a regular Date field for this. Set it once and don’t modify it after.

4. In a formula you can add the NOW() function which will return the current date that you can use in your formula. You can subtract your Date Due field from it to get the number of days remaining.

(Date Due - NOW()) / 60 / 60 / 24

That’ll give you the number of days between your Date Due field and the current time.

Click the Refresh button at the bottom of the records list view to refresh the NOW() values for all your formulas that use that function for all the records in your form.

[Mike Schwartz]

tonyt,

Item (1) If I understand you correctly, you WANT the field for Item (1) to update automatically. You can create a calculation field and assign it the formula “NOW()” (without the quotes), set the result type to Date, and set the Date format to a format that doesn’t include a time display, and leave the Time format set to <Default>. This will result in a field that displays today’s date.

Item (2) As an alternative approach for Item (2), you can use a simple Date field, and specify a default value as “Set Current Date”. There’s nothing in Tap Forms that would cause this field to be updated automatically, but you are still free to edit the field if you need to (in case you ever need to fudge the date for some good reason). The Date Created field type cannot be modified, which could either be a blessing or a curse depending on your needs.

Item (4) Use the formula provided by Brendan; specifically incorporate the NOW() function and don’t substitute your Today field. To elaborate further, set the Result Type to Number, and set the Number Format to Decimal Style with 2 decimal places. Note that date/time is represented internally in seconds, so you do the subtraction first and then the divisions to convert the difference from seconds to days. If you don’t show the decimal places for the field, the displayed number in this field will round up or down and possibly give you a misleading result. For example, if the due date is tomorrow and the current time today is 6:00 PM, then you’re 0.25 days away from the due date. If you don’t show the decimal places, the field will just display 0.

Hope that helps,
Mike

[Leo]

This is just what I’ve set up in my example to you Tony.

[tonyt]

Many thanks ALL for your help. Nearly there! All working fine on the Mac but on th iPad the days overdue are showing many decimal places, whereas on the Mac there are none. I have checked all the settings and they look the same. What have I missed on the iPad to prevent rounding?
Cheers
Tony

[tonyt]

Sorry, sorted! Number format on iPad not set to decimal.
MANY thanks again all
Cheers….

[Author]

Posts