Date Difference Negative Number

[Reid]

We have some employees that work from 11pm to 7am. They put this information into an In and Out field. When Total Time is calculated it is negative.

To get around this I have created a Date In and Date Out field. Both are set up to autofill with the created date.

I'd like the Total Time to have a script that when the Out - In = negative then Date Out is advanced by one day thus solving the problem. I will then hide the Date In and Date Out so that the user won't have to even mess with them.

For Example: In time is entered as 11:00 pm and Out time is entered 7:00 am. Both the Date In and Date Out are autofilled with the date 8/20/2010. When calculated there is a negative Total Time. The script would automatically change the Date Out to 8/21/2010 thus solving the negative number issue and correctly calculating 8 hours Total Time.

So I'm looking for something like this:

If (Out - In)=(-) ; (Date Out + 1 day) ; do nothing

[Lee Smith]

Maybe you missed this Thread the other day, see if it isn't what you wanted. Link

[Reid]

I saw that post but that formula doesn't work for me.

When the date_in and date_out are both 8/20/2010, the time_end is 12:45pm and the time_start is 11:00am the total time is calculated as 0:00:01.75

I was able to tweak the formula to get the correct hours using (((Date Out - Date In) * 86400) + Out - In) but that doesn't solve my problem.

If there is a way to write a script for what I want it will be better because the user will not have to enter a date_in or date_out (since I don't have appointments over 24 hours). It wouldn't matter what the date is as long as they are the same and when the total hours using the tweaked formula above ends up negative then it would advance the Date_out automatically by one day.

Does that make sense?

[Reid]

Ok...I was able to get yours to work. I had the Total Time formated to time instead of a number.

Still though, the user has to put in various date fields. 99% of the appointments don't require overnights. If I could get a script for my idea it would be better for my situation.

[Reid]

Here's what I am trying to do using your formula:

IF (((Date Out - Date In) * 86400) + Out - In) /3600 = -* ; Date Out + 1 then (((Date Out - Date In) * 86400) + Out - In) /3600; (((Date Out - Date In) * 86400) + Out - In) /3600

[Lee Smith]

I'm not understanding your problem, why not post a copy of your file, or a mockup.

[Reid]

I figured it out. Here's what I came up with.

This works for overnight shifts (less than 24 hours on 2 different days)

All the user sees the sign in field and the sign out field. Both are times. So In could be 11:00pm and Out could be 7:00am. The Total Time field automatically calculates 8 hours.

Here's how,

I have two hidden fields called Date In and Date Out. Both are set to autofill the creation date. So both will always be equal.

Another hidden field is Total Time Calc. This has the formula (((Date Out - Date In) * 86400) + Out - In) /3600

Set to number format.

Another hidden field is Total Time2 which has the formula If (Total Time Calc < 0 ; Date Out + 1 ; "")

Set to Date format.

Then my final field called Total Time which is visible has the formula If (Total Time Calc < 0 ; (((Total Time2 - Date In) * 86400) + Out - In) /3600 ; (((Date Out - Date In) * 86400) + Out - In) /3600 )

Set to Number format.

In this way the user doesn't ever have to worry about putting in a date that they entered and a date that they left. It just calculates the number of hours.

(I do have another field which they enter the appointment date but it would only have the start date which is all they would care about anyway)

Thanks for the help. I hope this add-on using your formula can help others!

[comment]

I have two hidden fields called Date In and Date Out. Both are set to autofill the creation date.

If someone worked from 11pm to 7am - they could only enter this at the time their shift ended, wouldn't they? So advancing Date Out by one day causes the entire shift to be moved - incorrectly - to the next day.

I do have another field which they enter the appointment date but it would only have the start date which is all they would care about anyway

Then why not leave it at that and calculate the elapsed time simply as =

Out - In + 86400 * ( Out < In )

or, if you want the result to be the number of hours =

( Out - In ) / 3600 + 24 * ( Out < In )

[Reid]

Amazing! Your's does what mine does only 100 times easier. Where were you 12 hours ago!

Thank you for your simple solution. Works great.

[Lee Smith]

Hi comment,

I'm getting an incorrect answer in your first calculation, am I missing something?

The second calculation works fine.

[Lee Smith]

I don't know why you didn't use one of the calculations I provided in the link I gave you on [color:red]08/20/10 04:48 PM, they all provide the same answer as what you came up with in your long calculation.

[comment]

I'm getting an incorrect answer in your first calculation, am I missing something?

I don't know. Why don't you provide some information, such as:

In = ___

Out = ___

expected result = ___

actual result = ___

[Lee Smith]

Here a sample file

TimeSheetCalc.fp7.zip

[Raybaudi]

Hi Lee

as comment pointed out, the second one returns a number, the first one returns a time. ( but you have "number" as result type )

[Lee Smith]

Hi Daniele

I knew I was missing something simple. But I just couldn't spot it.

Thanks for pointing out my error.

Lee

Edited August 21, 2010 by Guest