Salesforce and other SMB Solutions are coming soon. ×

# Time calc past midnite

## Recommended Posts

I just know that Chopper will have the answer to this one!

I have a database where I record a total amount of time spent based on a calculation of "end time minus start time". Times are entered based on a 12 hour clock with AM and PM notations. My problem is when one lists say 8:00 PM to 12:00 AM the calc returns a negative number. I know it's doing this because it assumes the times are both from the same day.

How can I account for times spilling over to the next day and keep my calculations correct?

Karl

##### Share on other sites

You can try this:

endTime - startTime + If(endTime

##### Share on other sites

Or you can add a calc field called

Elasped Time = ((End Date - Start Date) * 86400) + End Time - Start Time

You will have to set your times and dates with a Start Time script and a Stop Time script

##### Share on other sites

This is mine:

If(begintime > endtime;(endtime-begintime) + 86400; endtime - begintime)

##### Share on other sites

This is mine:

If(begintime > endtime;(endtime-begintime) + 86400; endtime - begintime)

##### Share on other sites

Paul,

That's the same logic as mine except that I have abstracted the repetition of the "endTime - startTime" part of the calculation:

Yours = If( test, A + 86400, A )

Mine = A + If( test, 86400, 0 )

where A = endTime - startTime

It would be a very minor speed improvement but the logic is worth noting for larger nested calculations.

##### Share on other sites

Everyone,

Thank you for the help. I tested your suggestions and find David's works the smoothest and fastest.

Thank you all again

Karl

##### Share on other sites

• 4 weeks later...

err guys i cant figure out why you need the 86400 for what is it stand for ?

##### Share on other sites

86400 represents the number of seconds in a day.

this part is needed in order to calc time that carries over into the next day.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Images

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

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