We help IT Professionals succeed at work.

Calculate by Hours and Half Hours

asp_net2
asp_net2 asked
on
Hello Experts,

I need an Excel File that will give me a total based on 2.45 for one hour and 1.23 for a half hour. Not sure what the best method to go about this would be. But I have a babysitter who works 9 hours a day and if she works a full 9 hours then she makes $22 for that day. However, she is an hourly employee so if she onlyl works for 4 hours then I need to calculate 4 hours of work. This is based on two weeks so a calendar may not be the best approach. I was thinking that I could add the whole total hours she works in a cell and then calculate all the cells for a total or have it do a running total.

1 Hour = $2.45
1/2 = $1.23
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
If you multiply 9 by 2.45 you get 22.05 so your calculations will be slightly out using that figure, does that matter to you?

In general if you have hours worked in one cell, e.g. H2, in time format like 4:30 and the hourly rate is in J2 then you can get the tiotal pay using this formula inj another cell

=H2*J2*24

If you have the hours in decimal format, e.g. 4.5 for four and a half hours then you don't need to multiply by 24 so the formula would be simply

=H2*J2

either way format the result cell as currency

regards, barry
John EastonDirector

Commented:
There may be a easier way to do this, however I think the below formula should do what you are looking for:

=(INT(A1)*2.45)+IF(A1-INT(A1)>=0.5,1.23,0)

This assumes that A1 is the number of hours and therefore an hour and a half would be 1.5 etc.  For each hour worked it will add 2.45 and if she would 0.5 or more hours it adds 1.23.

Obviously change the reference to A1 to the cell that actually has the total hours in.

Author

Commented:
@JEaston / barryhoudini:

Sorry, I'm not use to Excel at all. I know, it's very sad :) I will try to figure out what you both are saying. I just need to make sure that if she works one hour then she gets paid $2.45 and half hour $1.23. I need to make sure nothing gets rounded up, it needs to be exact by the penny.
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
>it needs to be exact by the penny

but you need to reconcile that with what you have said. What should be paid for 9 hours. You said it was 22.00 but if you use 2.45 multiplied by 9 you get 22.05, which is correct?

What about 8 hours?

If you want to pay exactly 2.45 per hour them use the formulas I suggested above. If you round up to the next "penny" then that will also account for 1.23 for half hours, e.g. use

=CEILING(H2*J2,0.01)

then if H2 is 1.5 (1 and a half hours) and J2 2.45 you'll get 3.68 as expected

regards, barry

Author

Commented:
@barryhoudini:

Hello, Ok, I finished creating my Excel Spreadsheet the way that I needed. I will need to start on Cell C7 through C17 from November 1 - 15th. However, what happens if C17 is longer or shorter based on the days of the month, how to I configure those calculations based on days of months?
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
Do you have the hours for each day in C7:C17?

If so then you could sum those somewhere else, e.g. with this formula in C20

=SUM(C7:C17)

then you can multiply by the hours in another cell.....or you can do both of those in one step

regards, barry

Author

Commented:
No, It's an on going day to day adding of hours. I have to wait until end of day before I can add hours.
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
OK, I'm not sure how you have it set up - can you post the spreadsheet.....or a version of it?

barry

Author

Commented:
attaching finished spreadsheet. You will see that I already added 9 as the total hours worked for 3 days but the other days may vary which is why I won't know the hours worked until end of that day.
EE.xlsx

Author

Commented:
You will also see that I created a sheet name for November. I would like to use one excel file but will multiple sheets using the same format. However, each months days will change some shorter/longer than others. Other than that, I don't plan on changing the format.
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
You could automate the dates a little, e.g. with the 1st of the month in A4 which you already had you can use this formula in A7 copied down

=WORKDAY(A$4-1,ROWS(A$7:A7))

to give you successive weekday dates - formatted as dddd to show the day like "Tuesday" - then in B7 copied down just get the same date

=A7

and format as d to show the day of the month

I put this formula in C19 for the total

=CEILING(SUM(C7:C17)*B$1,0.01)

which should work assuming that half hours always pay half of the hourly rate (rounded up).

That will update as you add hours to each day

Now you can copy the sheet by right clicking the sheet tab and using the "Move or Copy" option....then rename sheet and just change the A4 date and the days will populate automatically -I did that for December - see attached

Do you need the 2nd half of each month?

barry
hourly-pay.xlsx

Author

Commented:
Hi barryhoudini,

Yes, sorry, I forgot about the second half for each Month :(
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
Do you want separate sheets for each half or both halves on a single sheet.....but with separate totals? If it's the latter try the attached.

As before just copy a sheet and rename then enter 1st of month in A4 and all dates should update automatically

regards, barry
hourly-payv2.xlsx

Author

Commented:
Hi barryhoudini,

I like it the way you have it now :)

Thank you!
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
OK can I clarify? Is the last version OK or do you want the month split in to 2?

regards barry

Author

Commented:
Not sure what you mean by split in to 2. The last version you attached 37083152 already has a month split into two sections within the same sheet. Is that what you mean?
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
No I wasn't sure if you wanted 1-15 November on one sheet and 16-30 on another?

regards, barry

Author

Commented:
The only question I have left is how to I use the format you created for another month in a new sheet?

CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
It's simplest to use one of the months already there as a "template" - for example to create January 2012 just right-click on November tab at the bottom and select "Move or Copy".

When the next box comes up slect "Move to end" and tick "create a copy" - this will create a new sheet called November 2011 (2). Right click on the sheet name again and this time select "rename" and type in the new sheet name January 2012. Now in that sheet you can just change the date in A4 to 1/1/2012 and the dates for the month will populate automatically - delete existing times in column C and you are ready to go for that month

regards, barry

Author

Commented:
Thank you!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.