rounding up time calculations

[littlered136]

Hi 

I have a database that is meant to work out time calculations. I have set the fields that require time to be entered as time fields and changed the way they are displayed as hhmm, but they display as 0.00 in the field, till you click on the field then it shows the time entered , then when you click on a different field goes back to 0.00

 

I also put the fields that have the calculations set to time results rather then number so they would display using time rather then decimal, it displays them as an odd calculation, but whiny set it to display to hhmm as well they also just show up as 0.00

I am not sure what I need to do to fix it. as I thought setting them to time would work out the calculations in time??

 

thanks

 

sarah

Work Times.fmp12

[rwoods]

Time needs to be entered as 00:00, rather than 00.00.

Is it anything to do with that maybe? I couldn't really understand you sample file, there were no calculated fields in it.

[littlered136]

Hi

Thanks for the reply. that worked with displaying them and then saw the 1 in the calculation was taking 1 second off so just took how many seconds are in one hour so the calculations are now correct Thanks

My calculation fields were pre call, post call, overtime and broken. Just trying to work out now how to get it to round up the calculation to the next 30 mins on the clock.

Really appreciate the help above, it was stumping me why it was not working and was so simple now you have said it. Thanks

[comment]

14 minutes ago, littlered136 said:

Just trying to work out now how to get it to round up the calculation to the next 30 mins on the clock.

A time value is treated in a calculation as the number of seconds elapsed since midnight. To round it up to the next half-hour, use =

Ceiling ( yourValue / 1800 ) * 1800

 

 

[littlered136]

Hi

 

I tried that and for some reason it put the value as 0 no matter what time I put in for leave and call when tested on the overtime field.... 

tester 2.fmp12

[Lee Smith]

what is the Password and user?

[littlered136]

opps sorry lee i attached the wrong file... that is a different database for something else.. this is the right one.. no username or password!!

 

thanks for taking time to look at it..

 

Work Times 2.fmp12

[Lee Smith]

I sent you a private message.

[littlered136]

Hi 

Lee from the forums kindly modified my database to help explain what the fields mean in my database. each field has a comment in the manage database section.

All my calculations currently add up as I need them to correctly, but what I now need to do is to have the following fields round up to the half hour mark.

Pre Call, Post call, broken, overtime. We are paid in half over increments for overtime which is why i need it to round up, so if the pre call is 2 hrs and 15 mins, I would need it to display 2hr and 30 mins.

Below explains what each field is for as I am aware they are not the normal timesheet fields. The descriptions are also in the comments section in manage database for each field to help match them up.

call - is my call time/arrival

unit call - this is the call for the whole unit and meant to be on set

wrap - this is the time we wrap on set

leave - this is the time I leave work

pre call - this is the amount of hours I am called before the unit call, so the difference between the cal land unit call less an hr cos we give an hour for free. So if my call is 6:00 and Unit call is 9:00 I will be on two hours of overtime - cos of of the three hours is free.

post call - this is the amount of time I give after wrapping. so if we wrapped at 6pm and I left at 8pm, my post call is 1 hr in the field, because its one hour free after that everything else is paid. 

broken - after I leave work I am meant to not be called for 11hr, so the call time next day should be 11hr later, this field works out if it is less then the 11hr and puts how many hours less (broken)

on some jobs they don't pay pre calls and post calls separately, they just say you shoot for 12 hrs then you give two hours free and after the two hours are up you get overtime. So it might sometimes be different to the pre call and post calls added up... thats why the calculations takes of 14 hours worth of seconds.

 

In the overtime field I had an attempt at trying to round up, but it just seems to make it display 0, but I know thats not correct. Any help please. Thanks

Sarah

 

Work Times 2 Mod.fmp12

[Lee Smith]

Hi Sarah,

I merged your two questions as they are for the same need.

Lee

[littlered136]

Thanks 

[comment]

I cannot open .fmp12 files so I cannot help you with your file. It doesn't really matter, because:

If you have a calculation that correctly calculates the overtime, then all you need to do is restructure it as:

Let (
overtime = « your current calculation goes here »
;
Ceiling ( overtime / 1800 ) * 1800
)

This is assuming your calculation is set to return a result of type Time.

 

Edited July 29, 2016 by comment

[littlered136]

comment thank you so much, that worked perfectly!!!!! They were set to time results......

I have another question about the calculation if you don't mind. 

Is it possible in within a time calculation or any calculation that if you were about to check one of two option that it then used that calculation rather then the other one. 

For example. say we worked for 12 hours then the current overtime calculation would work, however if we were on the next day on a 10 hour day the formula would not work as it would be assuming I was on a 12 hour day. Is it possible therefore to have 2 fields within the time sheet layout that you check one of them and it choose that calculation in the overtime field depending on which box is checked. Does this make sense and is it something that is possible. 

Many thanks for your help so far!!

[comment]

10 hours ago, littlered136 said:

Is it possible in within a time calculation or any calculation that if you were about to check one of two option that it then used that calculation rather then the other one. 

Yes, of course that's possible. I don't follow your particular example but in general, suppose you have field A (Number), field B (Number) and field Operation (Text), you could have a calculation that does =

Case (
Operation = "add" ; A + B ;
Operation = "subtract" ; A - B ;
Operation = "multiply" ; A * B ;
Operation = "divide" ; A / B ;
"?"
)

 

[littlered136]

HI Comment

Sorry let me try and explain better as I am not sure how to put the rounding up calaulcation together with the case function. I have attached a screen grab of the layout.

What I would like the overtime field to work out is the following. 

after I have worked either 12 hours or 10 hours how much overtime I have done. I would want to check either the 12 hour or 10 hour check box. So if I check the 10 hour check box, I want the overtime field to calculate overtime after 10 hours, using the call and leave fields to work out the hours I worked. If I check the 12 hour box then to calculate the overtime after 12 hours. 

This is the calculation I have in the overtime field so far, to work out overtime on 12 hours and rounding it up to the next 30 mins

Let ( overtime = Leave - Call - 50400
 ; Ceiling ( Overtime / 1800 ) * 1800 )

 

So I am not sure how to use the case formula within this. I know I would need a calculation that has different second count for the 10 hours as well. 

 

Does this description and the screen grab help explain what I am trying to go? I uploaded the file as well just in case you can now open it, but did a screen grab of the layout to hopefully help my explanation???

 

Thanks for your help so far it is very much appreciated!

Work Times 2a.fmp12

[comment]

1 hour ago, littlered136 said:

This is the calculation I have in the overtime field so far, to work out overtime on 12 hours and rounding it up to the next 30 mins

Let ( overtime = Leave - Call - 50400
 ; Ceiling ( Overtime / 1800 ) * 1800 )

Okay, so first of all, I see two problems with what you have now:

1. 12 hours is 43200 seconds. You have 50400 and that's 14 hours.
2. When the total duration is less that the overtime threshold, your calculation will return negative overtime - and, presumably, fine you handsomely for the "missing" time.

So I would start with this:

Let ( 
overtime = Max ( Leave - Call - 43200 ; 0 )
;
Ceiling ( overtime / 1800 ) * 1800
)

Now, if you want to have a field for the overtime threshold, instead of hard-coding it into the calculation, you can change it to:

Let ( 
overtime = Max ( Leave - Call - DayLength * 3600  ; 0 )
;
Ceiling ( overtime / 1800 ) * 1800
)

where DayLength would be a Number field, and in your example it would contain either 10 or 12 (although it could be any number of hours).

Note that if DayLength is empty, then the entire duration will be considered overtime - so you will want to make sure that doesn't happen.

[littlered136]

Hi

 

Sorry my fault, I forgot to say we give an hour free on either side of the day, hence calculating it after 14 hours. Thank you for the formula above, I can simple put 14 hours in the number box.Thank you for also explain it, as it means I am learning why you are doing things. Many Thanks. I really appreciate you giving me your time!

[comment]

8 minutes ago, littlered136 said:

 I forgot to say we give an hour free on either side of the day ... I can simple put 14 hours in the number box.

Or make:

overtime = Max ( Leave - Call - ( DayLength + 2 ) * 3600  ; 0 )

 

[littlered136]

wonderful, thank you! may I ask one last question. Is there a way that when its on the perfect hour that it doesn't display the 0 seconds part. I know you can change it in the data tab to hh rather then he, but then of course when it's 1:30 (1hr 30 mins) it would not show the 30 seconds on it. It is just for neatness and easy of reading it in the report. I just wanted to heck if this was possible before i carry on trying to work it out, in case it wasn't and therefore I would never get there in my tries..... so when it's just 1hr it shows 1 rather then 1:00.....

Hope this makes sense

thanks

[comment]

2 hours ago, littlered136 said:

Hope this makes sense

Not really. The only way I can make some sense into it is if I read "minutes" where you wrote "seconds".

It is not possible to format a Time field (incl. a calculation field that returns a result of type Time) conditionally so that it displays minutes only when they are not zero. To achieve this, you would have to use a calculation field that returns a result of type Text, and calculate the required output in each case, for example:

Hour ( timeValue ) & If ( Minute ( timeValue ) ; SerialIncrement ( ":00" ;  Minute ( timeValue ) ) )

However, if you modify your overtime calculation to return such result, you won't be able (at least not easily) to use it in further calculations, such as calculating the overtime pay.

BTW, I am not sure why such display would look more neat on a report than 1:00, 1:30, 2:00, etc.

[comment]

3 hours ago, comment said:

It is not possible to format a Time field (incl. a calculation field that returns a result of type Time) conditionally so that it displays minutes only when they are not zero.

But, on second thought, you could use two instances of the same field - one formatted as hh, and the other as hhmm - and hide one them conditionally.

[littlered136]

How spooky, I was just sitting there thinking hmmmm I wonder if conditional formatting would allow me to do something..... 

 

so I did a test and did a second field to test on the pre call field and called it pre call copy. I did it to hh, and I set up the conditional formatting but not sure if I am choosing it correctly as I cannot seem to get it to work. I have attached some screen grabs showing the different fields.... once it is working I would lay the pre call copy field over the pre call one so they display in the same place on the screen.

I also have a previous formatting in it you will see where if it is 0 or less it displays the text as white so it's blank in the reports it generates.

would it not be the ending in .00 with white text to make it disappear, and for the pre call copy field I am not sure what formatting to put on it to make it white when it would be say displaying 1 when the hhmm field is showing 1:30. Does this make sense along with the photos??

 

thanks

 

 

I got it to work.... for both... I remember what you had said earlier about filemaker pro working in seconds only so added the 00:00 rather then just 00 and for the hh formatted one I put :30:00 and now they both work..... only managed to work this out cos of all the help you have given me this weekend which is allowing me to start seeing and figuring things out.. thank you so much

[comment]

You can do it with conditional formatting - but you should do it with object hiding:
http://www.filemaker.com/help/13/fmp/en/html/edit_layout.10.22.html#1171261

Hide the hh field when:

Minute ( Yourfield )

and hide the hhmm field when:

not Minute ( Yourfield )

 

26 minutes ago, littlered136 said:

did a second field to test on the pre call field and called it pre call copy.

Not sure what you're saying here. You only need one field for this. You just place the same field twice on the layout.

[littlered136]

oh yes that is much better then conditional as that was not allowing the text to be no fill, when set to no fill it was showing up as white so you could see white lines of the text going over the field underneath..... this works perfectly..... thank you ... I have learnt a lot this weekend!!!

 

I also thought I had to have two different fields with the same calculations overlaid so thanks for pointing out just one and format differently....

and the last issue I am having is say I leave after midnight I am putting 00:30 for example in the leave field, it then thinks I am doing no overtime as its blank (it is formatting to be white text when the result is 00:00:00.... is there a trick so it knows its the next day signing out?? Thanks

[comment]

21 minutes ago, littlered136 said:

say I leave after midnight I am putting 00:30 for example in the leave field, it then thinks I am doing no overtime

Instead of:

overtime = Max ( Leave - Call - DayLength * 3600  ; 0 )

do:

overtime = Max ( Leave - Call - DayLength * 3600 + ( Leave < Call ) * 86400  ; 0 )

This is assuming you never do more than 24 hours in one stretch.

[littlered136]

ekkkkk If I am doing more then 24 hours I need to get a new career

 

would this also work if we work nights one week instead?? as we would be called say 18:00 and leave 6:00

 

when I put the above in it puts this and will not let me change the calculation......

I am leaving the 'Let' in right??

 

 

[comment]

Are you just copy/pasting here? Or are you trying to understand how this works? Because this should be fairly obvious to you by now.

[littlered136]

Nope I did it from scratch as I then also added in the plus 2 on the daylight to account for the 2 hours free after that attempt above in the screen grab... 

 

I was not sure what it meant by too few parameter as I thought it was all there... 

 

I will keep plugging away... thanks

[littlered136]

doh!!! worked it out, finally managed to get some sleep as I was at work last night... fresh brain and saw right away what I had done wrong.....  Thanks, sorry about my blonde moment!!!