Salesforce and other SMB Solutions are coming soon. ×

Time Differance

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.

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.

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?

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)

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.

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)

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 )

)

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

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?

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 ) ;

)

)

Share on other sites

Thank you so much that work great!

Its perfect

Archived

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

• Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• Forum Statistics

• Total Topics
33.7k
• Total Posts
141.6k
×
×
• Create New...