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

Time Differance


Diedora

Recommended Posts

Hi, I have a field with time and date and we use this when we open a service request. We also have an other field which we enter when it is complete. Is there a way of making a calculation which would tell me how long it took for the job to finish.

 

Thank you.

Link to comment
Share on other sites

How_long=EndDate-StartDate. This will give you how many days if your fields is Date and how many secunds if your fields is Timestamp.

Link to comment
Share on other sites

Thx for reply, I tryed what you said but it did not work.

See what I have is 4 fields 2 of them are start date and finish date, the other two are start time and finish time.

I wish to make a field which would give me how long the job took to finish

 

Example:

01/06/05(Start Date)

13:00 (Start Time)

02/06/05(Finish Date)

15:30)Finsh Time)

 

total Time: 1 - 2:30

 

Is it possible to do such a calculation?

Link to comment
Share on other sites

Use the TimeStamp Function to convert your start and finish times into time stamps

Finish = Timestamp(FinishDate;FinishTime)

Start = Timestamp(StartDate;StartTime)

Then define ElapsedTime as Finish - Start and select the result as Time format. This will give you a result in the form hh:mm:ss. The hours will be greater than 24 if the number of days is bigger than 1. To convert it into days:hh:mm:ss you use

Days=Div(hh;24),hours=Mod(hh;24)

Link to comment
Share on other sites

Thx i nearly got it working(thx to yo)

 

Ok it all goes well but i dont understand were do i put

Days=Div(hh;24),hours=Mod(hh;24) this into?

I have the field which has hh:mm:ss

thx.

Link to comment
Share on other sites

SlimJim is correct, In 7 you can add or subtract timestamps. Pretty cool.....However in the old days of 6 or lower we used 5 fields

 

 

 

Date Stop

Date Start

Time Stop

Time Start

 

Elapsed Time = Calc =

((Date Stop - Date Start) * 86400 + Time Stop - Time Start)

Link to comment
Share on other sites

Try this (set result to Text):

 

Let ( [

elapsed = Timestamp ( EndDate ; EndTime ) - Timestamp ( StartDate ; StartTime ) ;

days = Div ( elapsed ; 86400 ) ;

rem = Mod ( elapsed ; 86400 )

] ;

days & " - " & Hour ( rem ) & ":" & Right ( "00" & Minute ( rem ) ; 2 )

)

Link to comment
Share on other sites

Hi Diedora

 

There is more than one way to complete the calculation and it depends on what format you need. For example let the ElapsedTime ET = 29:09:59 and do the following text calculation

 

DisplayET= If(Hour(ET) >= 24;Div(Hour(ET);24) & "-" & Mod(Hour(ET);24) & Right(GetAsText(ET);6);ET)

 

Then ETDisplay = 1-5:09:59

 

On the other hand with, again a text calculation,

DisplayET = If(Hour(ET) >= 24;Div(Hour(ET);24) & "-" & ET - Div(Hour(ET);24)*86400;ET)

 

Then ETDisplay = 1-05:09:58

 

And with Comments method you will get 1-5:09

Link to comment
Share on other sites

Thx so much I used Comments's solution;

 

Let ( [

elapsed = Timestamp_finish - Timestamp_start ;

days = Div ( elapsed ; 86400 ) ;

rem = Mod ( elapsed ; 86400 )

] ;

days & " - " & Hour ( rem ) & ":" & Right ( "00" & Minute ( rem ) ; 2 )

)

 

And it worked a charm, one thing thought, if the end date is not entered I get all these numbers (-17571898:04:26) can i make a if statment so it would show a zero or a message saying that it has not been closed?

Link to comment
Share on other sites

Sure. Try:

 

Let ( [

elapsed = Timestamp_finish - Timestamp_start ;

days = Div ( elapsed ; 86400 ) ;

rem = Mod ( elapsed ; 86400 )

] ;

Case ( Timestamp_finish ;

days & " - " & Hour ( rem ) & ":" & Right ( "00" & Minute ( rem ) ; 2 )

)

)

 

 

This will leave the field empty until Timestamp_finish is filled out. If you want a message, change the final part to:

 

...

Case ( Timestamp_finish ;

days & " - " & Hour ( rem ) & ":" & Right ( "00" & Minute ( rem ) ; 2 ) ;

"your message here"

)

)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use