How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Ryan-CTech Your Own Question

Ryan-CTech
Ryan-CTech, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 524
Experience:  Expert VBA Programmer.
18042535
Type Your Microsoft Office Question Here...
Ryan-CTech is online now
A new question is answered every 9 seconds

Start Finish total hrs overtime double 08:00

Customer Question

Start Finish total hrs overtime double
08:00 AM 10:00 PM 14 $0.00 $280.00

HI i am having trouble formulating my overtime rate that is the time and a half section
this is the formula am using for my double time rate and it works great except if the value is less than 10 it should be displayed as a $0value - not sure how to do this?
=IF(E10>10,E107.9,E11<9.9)*(10-E11)*$D$2
=if(hrs are less than 8 or greater than 10) then times (hrs - hrs) times time and half rate?????????
Submitted: 4 years ago.
Category: Microsoft Office
Expert:  Ryan-CTech replied 4 years ago.

yegarboy :

Hey there :) I can assist you with this.

JACUSTOMER-j0hb8ncd- :

great

yegarboy :

Could you review your formula you posted as an example and ensure the ranges are correct?

JACUSTOMER-j0hb8ncd- :

Sure so explain further

yegarboy :

If I understand your question correctly the formula you have now works except that it wont display a $0 if under 8?

JACUSTOMER-j0hb8ncd- :

I am working out a base sheet where i cost analyse daily performace and i have set up the page as a time sheet and then have my variables there so that i can calculate the times that they give me just by entering the hrs worked in total.

JACUSTOMER-j0hb8ncd- :

sooooo if i have a standard day of 8 hrs its multiplyed by standard pay rate

JACUSTOMER-j0hb8ncd- :

can i send you my xcel sheet?

JACUSTOMER-j0hb8ncd- :

there were two parts to the question -

yegarboy :

Ahh ok, in order to provide you with the best answer I can I will need to see your workbook. You can upload it to www.wikisend.com. After uploading your file copy the File ID or Download link and Paste it here :)

JACUSTOMER-j0hb8ncd- :

my first formula works out the dble time rate off the hrs because i know that anything over 10 hrs is dble time .... where the problem sits is that anything below 10 should say the value 0

yegarboy :

I got ya :). I need your book to work out the details :)

yegarboy :

If you can,....

yegarboy :

Highlight the cells that need the formula fixed :)

JACUSTOMER-j0hb8ncd- :

ok sure am just trying to figure out how to upload

JACUSTOMER-j0hb8ncd- :

http://wikisend.com/download/938000/electricians times.xlsx

yegarboy :

Did you follow the link I provided above?

yegarboy :

Ahh ok great

JACUSTOMER-j0hb8ncd- :

yep it didnt work?

yegarboy :

Yes sir :) I have it

JACUSTOMER-j0hb8ncd- :

sweet

yegarboy :

Ok now walk me through what it should do :)

JACUSTOMER-j0hb8ncd- :

alright so when you change the hrs to a number greater than 10 it should calculate overtime across the board

JACUSTOMER-j0hb8ncd- :

i have fixed the zero valu thing for dble time

JACUSTOMER-j0hb8ncd- :

the problem column is the time and half rate

JACUSTOMER-j0hb8ncd- :

so between 8 hrs and ten hrs i need it to calculate time and half

yegarboy :

Column G "Overtime"?

JACUSTOMER-j0hb8ncd- :

9 works

JACUSTOMER-j0hb8ncd- :

yep

JACUSTOMER-j0hb8ncd- :

8 should be 0 and 10 should be 105 but ive go it backwards

yegarboy :

ok

JACUSTOMER-j0hb8ncd- :

yeh so i need a formula that as i put in the total hrs worked for that line it knows to take everything worked above 8 and put it into overtime columns.

JACUSTOMER-j0hb8ncd- :

sometimes its 1 and sometimes its two and thats where my formula fails

yegarboy :

See if this does what you ant :)

=IF(AND(E10>7.9,E10<9.9),E10*D2,E10-10)

JACUSTOMER-j0hb8ncd- :

its also needs to ignore anything above 10 because thats dble time

JACUSTOMER-j0hb8ncd- :

really hang on

yegarboy :

Scrap that...my bad

JACUSTOMER-j0hb8ncd- :

i put that into cell G12 and changed the relevant things and it doesnt work out

JACUSTOMER-j0hb8ncd- :

=IF(AND(E12>7.9,E12<9.9),E12*D2,E12-10)

JACUSTOMER-j0hb8ncd- :

no problemeo

yegarboy :

I need an example total to work with :) E10 has 14 in it. What should the overtime be?

JACUSTOMER-j0hb8ncd- :

overtdime shopuld be 2 hrs and double time is the rest

yegarboy :

Ok thanks

JACUSTOMER-j0hb8ncd- :

no worries so it should only recognise the 2 hrs either a 9 = 52.5 or 10 = 105

JACUSTOMER-j0hb8ncd- :

8 = 0

yegarboy :

Sorry for the 20 questions it just helps me understand a little better :)

JACUSTOMER-j0hb8ncd- :

just fire away

JACUSTOMER-j0hb8ncd- :

brb

yegarboy :

I cant seem to wrap my head around around the formulas you have lol. Here is what I am not grasping....

JACUSTOMER-j0hb8ncd- :

ok

yegarboy :

I apologize but I will have to Opt out as I am out of time. Your new Expert will be here very soon :). They will see what we have discussed and continue from there. Do not rate until you get an answer that works :) Good night. Also...please dont reply back until your new Expert replies to you.