Day of Year

[Glen Forister]

DateCollected

I’m interested in obtaining the day of the year (1 to 365).
To do that I see how to insert another date field from the “Edit Formula” screen.
The “Date Collected” in the pic attached.

The problem is I don’t know how to get the difference (IN DAYS):
– from Date which might be any of the last few years or future year
and
– Jan 1 of that year (or any year – not worried about leap year).

My attached pic shows in that col (Day of Yr) the calculated days in text, but that got there from exporting out of another database program where it was calculated. But, future additions need to be calculated.

Any help? I hope it isn’t a program script. So far I haven’t had any luck with that solution on another Form.

Thanks.

• This topic was modified 2 years, 3 months ago by Glen Forister.
• This topic was modified 2 years, 3 months ago by Glen Forister.

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

Well, my first thought was a Javascript script… ;-) But then I wondered if the calculation field might do the trick. Yes, and here is the solution:

DAYS(NEWDATE(DATE([Date day Collect];"yyyy");1;0;0;0;1;0;0;0);[Date day Collect])+1

It looks complex but it isn’t:

1) DATE([Date day Collect];"yyyy") extracts the year from your Date day Collect field. The square brackets are only an optical indicator.
2) NEWDATE(...) creates the date for January 1st of your Date day Collect year.
3) DAYS() calculates the days between two dates, the Januar 1st Date day Collect and Date day Collect.
Then it’s just adding 1 to get the result. The return value is number.

Attached is the formula in my editor. I used your rain collection data where date equals Date day Collect in your graphic. I did this on my desktop.

Here is the link to the calculation field documentation: https://www.tapforms.com/help-mac/5.3/en/topic/calculation.

Have fun!

• This reply was modified 2 years, 3 months ago by Daniel Leu.
• This reply was modified 2 years, 3 months ago by Daniel Leu.

Attachments:

You must be logged in to view attached files.

[Brendan]

If all you want is the day number of the year, you can use the DATE() function with the appropriate pattern to get that value.

DATE([Date day collect]; "D")

The D will generate the day of the year for the specified date.

For a list of date format patterns you can use, see this page:

http://www.unicode.org/reports/tr35/tr35-31/tr35-dates.html#Date_Format_Patterns

There’s also a link to this in the online user manual in the Calculations topic.

[Daniel Leu]

Ahh… so much easier!

[Glen Forister]

Thanks guys. Nice.

[Glen Forister]

Well, Nice try, but no success.

All the formula DATE([Date day collect]; “D”) did was to delete all the data I had in that colum and all is blank.

Refresh again and again, quitting TF, starting TF, Refresh again, and the “Day of Yr” col is blank. – see attached pic with blank fields in the background.
What am I doing wrong???

Attachments:

You must be logged in to view attached files.

[Brendan]

Set the Result Type to Text. Because the Date() function returns a String, not a Number.

[Brendan]

If you need the result to be a number you can add the STR2NUM() function around it to do the conversion for you if you need to use that result in some sort of other numeric calculation.

[Glen Forister]

That’s great.
I forgot about numbers vs strings. I chose the STR2NUM so I won’t forget it isn’t a number and try to do some chart or other calc on it. Thanks for the hint. It works.

But, I can’t find any help for charts. When I try to chart Date (x axis) and another field of numbers, I get a blank chart.

[Brendan]

I’ve replied to your Charts question in your other post.

[Author]

Posts