AnsweredAssumed Answered

Bad time card calc. function and can't fix it

Question asked by Dekade on Jun 21, 2012
Latest reply on Jun 22, 2012 by philmodjunk

Title

Bad time card calc. function and can't fix it

Post

FMProADV 10.0v3 Win7 x64 Professional

I have tried to improve upon an already working database and have somehow goofed up two very similar calcualtions within the new update.

Look at .png - DB1 - This a good working database acting as a time card. The two concerned fields here (that are now working nicely in this DB1 database) are the "AM Work Hours" and "PM Work Hours" fields.

Here are the formulas for those two fields:

AM Work Hours: Round(Hour( End Time AM - Start Time AM ) + Minute( End Time AM - Start Time AM ) / 60 - Lunch Hours;2)+If(GetAsNumber(End Time AM) <= GetAsNumber(Start Time AM);24;0)

PM Work Hours: Round(Hour( End Time PM - Start Time PM ) + Minute( End Time PM - Start Time PM ) / 60 - Lunch Hours;2)+If(GetAsNumber(End Time PM) <= GetAsNumber(Start Time PM);24;0)

Again - everything about DB1 is working well. It represents a time card that can record "1st Shift Hours Only" - AM and PM hours and total them -or- just record AM and PM hours seperately.

Now look at .png - DB2 - I have tried to improve on DB1 because I need to have the availability to record "3rd Shift Hours" as well. DB2.png shows some strange field returns.

Here is what I tried to make work for 3rd Shift hours worked recordings. I have altered the two field calculations as follows:

Trial 3rd Shift PM Hours: Case ( IsEmpty ( Next Day Start PM ) and IsEmpty ( Next Day End PM ); Round(Hour( End Time PM - Start Time PM ) + Minute( End Time PM - Start Time PM ) / 60 - Lunch Hours; 2)+If(GetAsNumber(End Time PM) <= GetAsNumber(Start Time PM);24;0);
IsEmpty ( Start Time PM ) and IsEmpty ( End Time PM ); Round(Hour( Next Day End PM - Next Day Start PM) + Minute( Next Day End PM - Next Day Start PM ) / 60 - Lunch Hours; 2)+If(GetAsNumber(Next Day End PM) <= GetAsNumber(Next Day Start PM);24;0))

 

Trial 3rd Shift AM Hours: Case (IsEmpty ( Next Day Start AM ) and IsEmpty ( Next Day End AM ); Round(Hour( End Time AM - Start Time AM ) + Minute( End Time AM - Start Time AM ) / 60 - Lunch Hours; 2)+If(GetAsNumber(End Time AM) <= GetAsNumber(Start Time AM);24;0);
IsEmpty ( Start Time AM ) and IsEmpty ( End Time AM ); Round(Hour( Next Day Start AM - Next Day End AM) + Minute( Next Day Start AM - Next Day End AM ) / 60 - Lunch Hours; 2)+If(GetAsNumber(Next Day Start AM) <= GetAsNumber(Next Day End AM);24;0))

By looking at .png - DB2 you can see all the goofy returns I am getting. I'll admit I'm trying to get better at functions but still have a ways to go.

Can anyone help me get this right?

Thanks,

Dekade

DB1_DB2.png

Outcomes