Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Waiting Time Calculation


Nunzie23
 Share

Recommended Posts

Hello,

 

I was wondering if anyone could help me with a calculation as I have no idea where to even start with them, can't get my head around them. What I am trying to do is calculate the length of time waiting in months and days between a date of referral and the date of the first appointment. I have both of those fields in my database. Now, if that's was too basic what I also need it to do is, if the person has not received a first appointment yet and they are still waiting, I would like it to calculate the waiting time from the referral date to the current date and would need it to refresh this info each time a report is done or the person's information is viewed on the database.

 

Does anyone know how to do that calculation? I know where I need to enter the calculation, just don't know how to actually write the calculation.

 

Thanks in advance for your help.

Link to comment
Share on other sites

Apologies, as I am new to this site I didn't realise there was a seperate forum for calculations and I didn't realise you can see them in all as opposed to just the specific one you were in.

 

I'll do my best to make sure I post appropriately in the future.

Link to comment
Share on other sites

For the record though, I do see the link you have in your reply brings me to another question I posted, which is not a duplication of this one itself. Its a different question. I have the age calculation between the DOB and the current date. But what I need is the calculation in months and days for a referral date to the date of the first appointment, OR the current date if they have not received the first appointment yet. As there is that extra piece of info it needs to consider I was unsure how to create that calculation which is why I posted a seperate question. The duplication post you refer to was just about refreshing a calculation not storing it as opposed to how to create a new calculation as this post is.

 

As I am an entry level user I was trying to keep my questions seperate to not confuse myself when applying the answers.

Link to comment
Share on other sites

Ooops! My fault, sorry about that!

 

It's late and I'm not up for answering questions right now but if no one else addresses your issue I'll look at it when I'm awake.

Link to comment
Share on other sites

Hello,

 

I was wondering if anyone could help me with a calculation as I have no idea where to even start with them, can't get my head around them. What I am trying to do is calculate the length of time waiting in months and days between a date of referral and the date of the first appointment. I have both of those fields in my database. Now, if that's was too basic what I also need it to do is, if the person has not received a first appointment yet and they are still waiting, I would like it to calculate the waiting time from the referral date to the current date and would need it to refresh this info each time a report is done or the person's information is viewed on the database.

 

Does anyone know how to do that calculation? I know where I need to enter the calculation, just don't know how to actually write the calculation.

 

Thanks in advance for your help.

 

 

a) FirstAppointmentDate - DateOfReferral (where both are date fields) will yield a number representing the number of DAYS between the two dates. So for our formula let's use a Let statement so we have that value in days to work with:

 

 

 

Let (ElapsedDays = GetAsNumber (FirstAppointmentDate - DateofReferral);

 

 

b) "Month" is a rather floppy concept, mathematically. Do you want an average month of 30.4 days? Or literal nominal months (where from March 1 to April 2 is one month one day but Feb 1 to March 2 is also one month one day even though that is less time)?

 

 

Truncate ( ElapsedDays/30.4; 0) would give you the number of average months and Truncate ( Mod (ElapsedDays; 30.4); 0) would give you the leftover days. So...

 

 

 

Let (ElapsedDays = GetAsNumber (FirstAppointmentDate - DateofReferral); Truncate ( ElapsedDays/30.4; 0) & " months and " & Truncate ( Mod (ElapsedDays; 30.4); 0) & "days" )

 

 

use that as the formula for a calculation field, result type "text".

 

Is that what you had in mind?

Link to comment
Share on other sites

That works great and is ALMOST exactly what I need. I just need it to do one more piece, again, simple I'm sure, but I just dont know how to properly write it in. Your calculation works fine, but let's say there is a client who has not had a first appt yet so that field is blank, I would like it to return the months and days for the current date that they have been waiting so far and that this info would not be stored so that it always refreshes to give me the correct waiting period each time the record is viewed. So they may have been referred 1st of October, but as of today 17th November they still haven't had an appt so they have been waiting for 1 month and 16 days. Sorry, Am I explaining this okay?

Link to comment
Share on other sites

I had this calculation originally which someone helped me with:

 

Year(Get(CurrentDate)) -

Year(AcceptedDate5Plus) -

Case(Get(CurrentDate)

Date(Month(AcceptedDate5Plus); Day(AcceptedDate5Plus); Year(Get(CurrentDate))); 1; 0) & " Years, "

& Mod(Month(Get(CurrentDate)) -

Month(AcceptedDate5Plus) + 12 - Case(Day(Get(CurrentDate))

Day(AcceptedDate5Plus); 1; 0); 12) & " Months "

 

but didn't know how to edit it to reflect time between accepted date and 1st appt and if 1st appt blank to current dat. Or to add the days into the result either.

Link to comment
Share on other sites

Your calculation works fine, but let's say there is a client who has not had a first appt yet so that field is blank, I would like it to return the months and days for the current date that they have been waiting so far

 

 

Let (

[

EndDate = Case (IsEmpty (FirstAppointmentDate; Get(CurrentDate); FirstAppointmentDate);

ElapsedDays = GetAsNumber (EndDate - DateofReferral)

];

 

Truncate ( ElapsedDays/30.4; 0) & " months and " & Truncate ( Mod (ElapsedDays; 30.4); 0) & "days"

)

Link to comment
Share on other sites

Thanks for that. DO I add that to your previous calculation as well as it's not working for me. Also, is there supposed to be ) after FirstAppointmnet Date;

Link to comment
Share on other sites

Ok, sorry. I obviously was doing something wrong the first time. I started all over, got out of the program and went back into it copying and pasting your calculation and it worked great! Sorry for the confusion, not sure what I was doing the first time. I just said it has to be right this person knows their stuff, try again.

 

 

THANK YOU SOOOOOOO MUCH!!! You have NO idea how happy I am to have this resloved. :joyous:

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use