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 Ray Atkinson Your Own Question
Ray Atkinson
Ray Atkinson, Graduate Student
Category: Homework
Satisfied Customers: 1890
Experience:  Inner-city high school substitute teacher. Degrees in mathemetics, accounting, and education. Years and years of tutoring.
29937297
Type Your Homework Question Here...
Ray Atkinson is online now
A new question is answered every 9 seconds

calculating hours worked on an current excel

This answer was rated:

I need help with calculating hours worked on an current excel spreadsheet in order to calculate overnight shift..ex 22:00 till 05:00 should be 7 hours


 


 I have spread sheet available to review


Ray Atkinson :

I'm rather good with Excel. Do you have a way to upload the file so I can look at it?

Customer:

yes

Ray Atkinson :

Depending on your browser, you should have a paperclip icon above where you type that says "insert/edit image"

Ray Atkinson :

oh, good. Can I have the link?

Ray Atkinson :

Also, when do you need this finished? If you need it tonight, I can stay up. If not, I can get it done in the morning. It is 0311 local

Customer:

I need it before 6am

Ray Atkinson :

ok, I am getting the file. 6am what time zone?

Ray Atkinson :

eastern US?

Customer:

central

Ray Atkinson :

All right. 3+ hours

Ray Atkinson :

Hmmm. file conversion failed. Let me try again.

Ray Atkinson :

It is possible for you to save it as a regular .xls file rather than .xlsx ?

Customer:

let me check

Ray Atkinson :

Excel 2003 instead of Excel 2007

Ray Atkinson :

My aVersion of excel will not open it.

Ray Atkinson :

awesome.

Ray Atkinson :

Oh, that did not work. It came through garbled.

Ray Atkinson :

The first line is








PK!Çz—x [Content_Types].xml ¢( Ì&r dquo;ßJÃ0Æïß¡äVÚlDdÝ.¦^êÀù19]ËÒ$äds{{O³?ȨÂÞ4´9çû~Išo8^×:YÇÊšœõ³KÀH«*3ÏÙûì9½g
Ray Atkinson :

How about you describe what you are needing?

Ray Atkinson :

Are all the "in" times later than the "out" times?

Customer:

think I got it fixed

Ray Atkinson :

ok, let me get

Ray Atkinson :

MUCH better

Ray Atkinson :

Ok, dumb questions time. Are hours in as 24 hour clocks, as in not 4:00 PM but 1600?

Ray Atkinson :

May I add additional columns if I hide them?

Ray Atkinson :

(I use hidden columns to do intermediate calculations)

Customer:

did it work

Ray Atkinson :

the file looks great.

Customer:

thats fine...whatever it takes

Customer:

time is in military format

Ray Atkinson :

Excellent. That makes my job easier.

Customer:

what happens is that Im getting a -15 hours when it crosses over midnight cause it doesnt calculate it correctly. I attempted to add the dates into the calculations but of course I failed at it

Customer:

I just want it to read the hours correctly. so If I say 22:00 to 02:00 it sould say 4hrs

Ray Atkinson :

I have the calculations done for going over midnight. Now I am working on the overtime hours. anything over 8 in one period, right?

Customer:

yes sir

Ray Atkinson :

Ok,. give me a couple minutes.

Customer:

can we just leave the calculation for ot out...

Customer:

nvm...go ahead

Customer:

yea..lets not calculate OT

Customer:

becasue it could change, I rather enter it manaully

Ray Atkinson :

Well, I streamlined the calculation for overtime. See if you like it. I did set it up for being over 8 hours a day

Ray Atkinson :

If you do not, just copy and paste from the old file.

Ray Atkinson :

give me a sec to save it.

Customer:

ok

Customer:

I think the current policy is that anything over 40 is OT...so not over 8 in a day yet, got get to 40 first

Ray Atkinson :

oh! do you want me to try to implement that?

Ray Atkinson :

I don't think that would be hard.

Customer:

sure

Ray Atkinson :

Actually that is much easier.

Customer:

sweet

Ray Atkinson :

Give this a look:

Ray Atkinson :

Whoops!

Ray Atkinson :

40 hours a week, not per 2 weeks,.

Ray Atkinson :

bug fix

Customer:

looks good

Ray Atkinson :

Not good enough.

Ray Atkinson :

Now, it checks for 40+ hours per week, not for a 2 week pay period.

Customer:

awesome....

Ray Atkinson :

I forgot that payslip was for 2 weeks

Customer:

no problem...

Ray Atkinson :

If you look at row 18, you see the check for overtime for week 1

Ray Atkinson :

26 is for week 2

Customer:

got it

Customer:

can you upload the one you corrected?

Ray Atkinson :

If the time is like from 22:00 to 02:00, it does the 2-22 and get -20, but then adds 24 to get the proper answer.

Ray Atkinson :

I didn't?

Customer:

my bad...I got it...its late

Ray Atkinson :

You can unhide columns E and H to look at the formulas.

Ray Atkinson :

Not a problem. I enjoy playing with Excel. There is an old joke that fur trappers used sheets over their shoulders to cover their tracks and people use spreadhsheets to do the same thing now.

Customer:

funny

Ray Atkinson :

Sorry. Had box checked.

Ray Atkinson :

You can try to rate now, if you please?

Ray Atkinson and 3 other Homework Specialists are ready to help you

Related Homework Questions