Count lapsed days from a manually entered date field for an issues tracker


#1

I want to create an issues tracker base.
One column will be a manually entered submission date.
Column next to it will be “Time elapsed” field to show how long this issue has not been solved yet (in days).

How can I calculate the “time elapsed field” in days since the date entered in the “submission date field”.

I found this question here already:

But this works only with the creation date of the entry. It’s important to me that I can select the submission date manually, so this field should be a normal date field.


#2

Denis -

If you take another look at the question you reference, you’ll see I also provided a solution for when the submission date is not necessarily the record creation date:

I’m assuming your TaskIssuedDate field is a formula field with a formula of CREATED_TIME(). If so, then your ElapsedDays field would also be a formula field, this time with a formula of TONOW(TaskIssuedDate). This will display the current number of days since the record was created.

In this case, your SubmissionDate would simply be a date field, and your elapsed time formula would be TONOW(SubmissionDate).

Hope this helps!
Vann


#3

I looked at it but as far as I understood both the TaskIssuedDate and the ElapsedDays field are formula fields in this example. So that prevents me from being able to manually enter a date into the TaskIssuedDate (because it will always show the creation date only) I hope I’m making sense here.

What I want to do is have a date field column and the ElapsedDays field counts from that date the i entered into the date field.


#4

Sorry – I wasn’t very clear. In your case, simply create a field called TaskIssuedDate (or SubmissionDate or whatever) of type Date. That way you can manually enter whatever the initial (start) date should be. Then create a second field, ElapsedDays, of type Formula and set the formula to TONOW(SubmissionDate) where {SubmissionDate} is the name of the field in which you enter the initial date. The calculation works the same way; it simply needs a date – either calculated or manually entered – to calculate from.


#5

Now I finally got it. Thanks a lot, that does exactly what I was looking for.


#6

I was needing the same thing, however when I did this it’s showing it in months, is there a way to show it in days only? my formula is TONOW({Date In}). I am tracking how many days something has been in inventory. And this is rounding by months, so it isn’t 100% accurate.


#7

Wow, that’s really annoying. I’ve never used TONOW() in any of my bases, so I hadn’t realized it forced a colloquial, ‘friendly’ response.

Instead, use

DATETIME_DIFF(TODAY(),{Date In},'days')

That will give you the number of days between {Date In} and TODAY(). If you should ever need the time in a different unit than 'days', from milliseconds to years, see this list of unit identifiers.

If your responses appear slightly off, make sure both {Date In} and the formula field with the DATETIME_DIFF() function are set either to use or not to use GMT; mismatched settings will give you unexpected results.


#8

That is perfect. Thank you so much. It’s exactly what I needed.