Calculating salary (Hourly paycheck)

**Qayin** · 07-05-2010, 09:35 AM

Hello =)
i used to have a job that paid a certain salary per day, but sometimes the boss told me to leave after 4 hours, and sometimes after 8... so in order to see how much i make per hour all all i did was make make a "Worked from" and a "Worked untill" columns, Subtracted the "From" from the "Untill" and got the number of hours.

i used to write the times like this "15.75" (for 3:45pm/15:45) and just ignore any extra minutes by rounding up to down. and assuming i worked till 9:45pm, Excel did 21.75 - 15.75 = 6 hours.

Now i have a job that is 24 hours around the clock.
meaning i could work from 9pm, untill 2am. but calculating 2.0 minus 9.0 gives me -7, and no only is it wrong, since 9pm to 2am is 5 hours, it gives me negative hours.

id like for a way/formula to simlpy be able to write the exact time with mins too, Even if it 15:12, without having to convert 30 mins to half etc, and have excel give me the result in how many hours + mins i've worked that day.

TY!

**dominicb** · 07-05-2010, 09:44 AM

Good afternoon Qayin

...and welcome to the forum!!

As your working day will be spanning two days, you need to tell Excel what dates are involved.
In A1 put 05/07/2010 21:00
In A2 put 06/07/2010 02:00
Note this is for 5th / 6th Jul - your format maybe different.
In A3 put the formula =A2-A1
The result will be 0.21, which is decimals of a day.

To convert to hours, use the formula =(A2-A1)*1440/60

HTH

DominicB

**Qayin** · 07-05-2010, 10:02 AM

Hey there =)
first of all thanks for the super quick replay.

i tried what you did by copy and pasting on a new time sheet.
A1: 5/7/2010 9:00:00 PM
A2: 6/7/2010 2:00:00 AM
A3: =A2-A1
A4 =(A2-A1)*1440/60
A3 gave me the result 30.20833333
A4 gave me the result 725

now, i found the issue, my Excel formats dates as such Month/Day/Year
how can i convert it to see dates as Day/Month/Year?

2nd.
is there any way to not have to write the date each time?
even maybe if i pre-set each line to be 2 days?

for example, in my chart A1 is "Tuesday" A2 is "1st".
isnt it possible to somehow make that whole line count as
two day's, and therefor just writing in my "From" 21:30 and
in my "Untill" 01:12" and still get the right time?

**dominicb** · 07-05-2010, 10:39 AM

Hi Qayin

To display the date as day month year. set your cell format to custom, and then use something like :
dd-mm-yy
dd-mm-yyyy
dd-mmm-yyyy
dd/mm/yy
Experiment with those and see which you like best.

With regard to the days, if the shifts will be less than 24 hours, then this formula should work OK (again assuming 21:00 in A1 and 02:00 in A2) :
=IF(A2>A1, A2-A1, 1+A2-A1)
We can force it to hours using :
=IF(A2>A1, A2-A1, 1+A2-A1)*1440/60

HTH

DominicB

**Qayin** · 07-05-2010, 10:48 AM

Hey Dom, thanks again!

one more question.
everything is awesome, except the date, really.

I want to type the Day first, then / and then the month.
but after i changed the format to DD/MM and i type
5 (as in the 5th) / 7(as in july) and click enter, it then flips
the numbers and displaying 7/5.

and still calculates it as if the its the 7th of may

Does the format change mearly DISPLAY or also Excels
interpetation of my writing?

its so confusing!

**dominicb** · 07-05-2010, 11:02 AM

Hi Qayin

This is usually just the way Excel interprets the dates - you may have to change your system settings. To do this go to Start > Control Panel, Regional and Language Options. There is an option in here to format the date.

HTH

DominicB

**Qayin** · 07-05-2010, 11:49 AM

Originally Posted by dominicb

Hi Qayin

This is usually just the way Excel interprets the dates - you may have to change your system settings. To do this go to Start > Control Panel, Regional and Language Options. There is an option in here to format the date.
HTH
DominicB

Thank you jesus! now it finally works! WOOHOOO!!!

now i just have 1 more thing i need to solve.
I would also like the end result to show minutes, without rounding them up.
If i use this:

A1: 5/7/2010 9:30:00 PM
A2: 6/7/2010 2:00:00 AM
A3: =A2-A1
A4 =SUM(A2-A1)*1440/60

Results:
A3: 0.1875
A4: 4.5

is there a way to translate 4.5 to "4:30" or changing "=SUM(A2-A1)*1440/60" so it displays 4:30? (so that even if the result is with minutes that arent quarter hourly
they will show)

Thank you!
hopfully thats the last question!!!

**dominicb** · 07-06-2010, 03:28 AM

Hi Qayin

Let's just take this one step at a time. You want the results for fractions of an hour rounded down to the nearest 15 minutes? If so, then this is the formula you would use :
=FLOOR((A2-A1)*1440/60,0.25)

You have also asked about reporting hours in minutes rather than decimals. You can do this, however you would lose all functionality for adding up your hours within the spreadsheet. I would suggest that you only use this at grand total level, you can show the total time worked in a week in both decimal (eg 9.716) and time format (eg 9:43).

Would this be acceptable?

HTH

DominicB