Hi There,
I am struggling to get the formula correct for adding hours on a staff rota.
I have done alot of searching on here and have found a formula that does most of what I need, apart from one calculation which shows as an error.
My staff work spilt shifts - say starting at 12:00-14:00 then 17:00-01:00
I have found a formula that can handle that, but if the staff member only works in the evening say 17:00 - 01:00 then I get an error.
Also when adding the actual hours worked I get an incorrect amount.
I have attached the file with some boxes filled for you to see.
Many Thanks for any assistance in making this work!
So i have found a way to maker this work simply by adding extra columns and hiding them. This leaves me with my finalled tally hours in the hhmm format. For example the total hours are 10:00 - how to I change this value to the number 10, so I can mutlipy by the hourly rate?
Many Thanks
Hi Fingus,
To get the number 10 from 10:00, simply multiply the time by 24. 10:30AM x 24 = 10.5, for example.
Paul,
Thanks for your advice.
I however want to convert 10hours to the number 10 so I can multiply it, by an hourly rate.
Originally Posted by Paul
Your "total hours" might be 10:00, but if you select that cell it will most likely show "10:00:00 AM" in the function bar above.
Times are stored as fractions of 1 day, so all times are between 0 and .999. If you format a cell that has a time in it as a number with decimals, you'll see the decimal equivalent of the time shown.
10:00:00 AM is equivalent to 0.416667.
10:30:00 AM is equivalent to 0.4375.
If you multiply either by 24 you will get the correct number of hours for your calculation.
0.4166667 * 24 = 10
0.4375 * 24 = 10.5
etc..
As mentioned in a previous, although now missing, post, you could also use
=HOUR(A1)
where A1 holds the total time in hh:mm format.
=HOUR(A1) where A1 = 10:00 would result in just 10.
That was me.. but I deleted it realizing that there could be fractions of hours too..as you showed in your example, Paul.. Hour() only gives the hour portion and MINUTE() and SECOND() only give the minutes and seconds, respectively, as whole numbers
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hmm it still hasnt quite worked out, but maybe my steps before are not correct.
Thanks for your help.
If you post a copy of the updated workbook (with your hidden columns) we can take a look..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks