
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
I have one cell with a sum of time reduced by allowing staff to leave early on days where we're slow. I'm using the custom format [h]:mm. All I want to do is divide that number by 37.5 hours and I'm having a fair bit of trouble. Any pointers on what formula and/or syntax I should be using? I've tried simply doing =A1/37.5 but the result I'm getting back is not correct.
I'm looking to have the result represented as a number w/ 2 decimal places to indicate the FTE (Full Time Equivalent = 37.5 hours per week) we've saved.
Similar Excel Video Tutorials
Add Time by Hour, Minute or Day
 See how to use formulas to add any unit of time that you would like. Add a column of time values with units of Hours, Minutes, or Days. 1) ...
Multiplying & Dividing Fractions
 See how to multiply and Divide Fractions using Excel Key Points: 1.Multiplying fractions less than one will always result in a number less t ...
Excel Time Across 2 Days Trouble
 In Excel, see how to calculate the number of hours worked for the night shift when the In time is before midnight and the Out time is after midnight.< ...
Helpful Excel Macros
Format Cells as Time in Excel
 This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Similar Topics
Hi,
If I wanted to convert say for example 16:00 hours in a fraction of total full times hours how would I do it?
37:50 hours = 1 FTE (full time equivalent)
16:00 hours = ???
If it was just in normal numbers excel works it out fine, but as the 16:00 is in an 'hours' value I cannot simply divide by 37.5
Thanks
I am trying to calculate how many hours each member of staff is entitled to for bank holidays as they are pro rata to the number of hours worked. Cell A1 has each employee's working hours, I then divide it by 37 (possible working hours per week) and multiply it by a 7.4 hour working day to give the number of hours they can take for the holiday. The problem is that this is decimalised and I need to convert the decimal time into hours and minutes.
Can anyone help?
Hi
I am trying to find out how to do some basic calculations using time ( i.e. hh:mm:ss).
I have a sheet which shows the number of hours spent on a task each day which I need to add up to get a total for a month in hh:mm:ss format. I have found you can do this by setting the format of the cell where you add up the totals to [h]:mm:ss format. This works quite happily and gives you a total hours worked on that task for the month.
What I now want to do though is display this as a % of the total hours in the month spent on all tasks. So say you worked X days this month and you know each day was 9 hours long. X is a variable number say in Cell A1. You would have a formula to work out the total worked hours (9*A1) . However I can't quite find a way to calcualte this correctly in [h]:mm:ss equivalent format. I need to then divide the total hours worked on my task by this figure and display as a %. i.e what % of the total hours worked this month were spent on this task.
I am really struggling to work out a formula that can calculate the total hours for X days in the equivalent of [h]:mm:ss format and produce a correct result as a percentage.
Any bright sparks out there know how to do this?
Many Thanks
Hours Worked
Normal Hours
After Hours
Pay
Start
8:30:00 PM
8:30
3:30
5:00
3.50
5.00
23:54
Finish
5:00:00 AM
I am starting with a very simple spreadsheet just to figure out the mechanics.
As you can see, in B2 and B3 I have 2 time objects.
For normal Hours I have =D34+(B2>D34)B2
For After Hours I have =B3+(D34>B3)D34
Then as you can see I have a value of 3.50, which is a decimal conversion of the 3:30 hours using
=HOUR(D2)+((MINUTE(D2)*0.01)*(1.66))
But because this is still typically a time object, if I was to multiply it by an hourly rate, I will not be returned with the correct pay.
What I need is to figure out a way to make that 3.5 a number that I can multiply and I simply cant remember how to cast it as an int properly.
I did try =INT(E2)+(E2INT(E2))/0.6 but it looks ok, until you give it more than 2 decimal places, then it turns from 0.35 to 0.347
Any Ideas guys?
Hi, gang,
Been awhile since I visited.
Since, the economy seems to be in such a turmoil, management wants to cut down on overtime. What I'd like to do is have Excel calculate the time to leave early on Friday, so the total hours worked for the week is only 40 hours. I'm using one of Microsoft's Time Sheets. Cell H10 shows the accumulated hours over 8 hours from each day of the week.
What I'm looking to do is, create a cell formula that will show what time to leave early on Friday. I can't quite get it, from looking at other posts.
Any suggestions?
Thanks,
EJ
I am trying to speed up the way I do the time cards for the lady at the local tea room where I work part time. I see that some time back Bill Jensen gave a formula for making this calculation but it is a bit confusing. This is what he wrote:
If the times are in cells A1 & B1 and you want the elapsed time in C1, follow these steps:
In cell C1, set up a custom number format of [h]:mm
Enter this formula in C1: =MAX(A1:B1)MIN(A1:B1)
The square brackets around the h will insure that times over 24 hours are still reported as hours instead of days.
Also, the result of a date/time calculation can never be negative. I've used the Max and Min to make sure the earlier time is always subtracted from the later time. You could also use =ABS(B1A1) or simply make sure that B1 will always be greater than A1.
That is fine but if I do this using military times it comes out like this:
945
1330
385
945
1330
385
So the time from 9:45 to 1:30 PM is 385 but how does that compute to hours and minutes. I know if I work it out in my head it like 3hrs. 45 mins. but I can't see that in the number 385. I really don't have the money to spend on a computer program to do this for me. All I am doing is just doing the calculations for her. This is a very small place and only employs like 8 or 9 people and no one works 40 hours a week.
Can you give me some insight into how to read this or is there a formula that tell me hours and minutes even if that number appears as just a series of numbers. I just need the key to understand it.
I am smart enough to get myself into a lot of work but I am hoping that I am also smart enough to be able to set up a formula that will do the brain teasers for me.
Thank you,
Hi People:
I need help about this problem:
Cell Format for Column A1,B1 and D1 is Time
Column A1 = Start Time = 07:31
Column B1 = End Time = 15:29
Column C1 = Total Hours = 7:58
Column F1= Productivity
Column E1= moves = 122
In the column C1 I'm using formula =IF(B1<A1;A1+1;A1)B1
the result is as above (column C1)
The problem is the result on column F1 that is when E1 divide by C1(change format to general) = 367.531380 and if column C1 i change to time format the result is 12:45. All this is not the answer I required.
The answer I need is like below:
Manual calculation
1 Converting 7:58 hours which is :58min to hours (58/60)=96 hours
=7.96
Which is 122 divide by 7.96hours
Suppose the result will be 15.326633
Can anyone give an idea about the formula ? I would appreciate it
Thanks
Hello,
Excel 2003
How to calculate hours across several days using full date time value
after using +now() function.
For example:
1. when a1 is start value: 21.11.2009 19:15:34
and b2 end value: 23.11.2009 09:20:51
By Excel default result is 14:05:07, which doesn't represent hours correctly.
While expected result should be somewhere 38 hours.
How to calculate hours (fractions in decimal)?
Tia
Hello All,
I am trying to create an Excel spreadsheet for my partner to work out her hours worked. However, for some reason unbeknown to me they pay her for incomplete hours worked in a decimal value. Complete hours are paid on an hourly rate as per normal.
The TOTAL HOURS are easily worked out using Excel however; I am having trouble trying to work out the MINUTE value into a decimal value.
START TIME
END TIME
START TIME
END TIME
TOTAL HOURS
DECIMAL VALUE
07:20
12:30
13:00
17:30
9:40
9.67
It is worked as follows using the values in the table above:
Whole hours remain as gained from (TOTAL HOURS) + (.40/60 x 100) = 9 + 0.67 = 9.67
I am not even sure if it is even possible to do this in Excel?
I.E. splitting one value (TOTAL HOURS) into two different values (HOURS & MINUTES) and treating each value differently (whole hours remain untouched and the minutes are manipulated as explained above) then bringing them back together for a final value (DECIMAL VALUE)?
Any ideas or directions to solve this would be gratefully received.
Regards,
JC1
Hi
I am entering data into a timesheet created in Excel 2007.
I have 2 problems that I need help with please.
Firstly, I am entering the time as a decimal, ie. 12.30  20.30 (being twelve thirty PM  eight thirty PM). In this case, I want to keep entering the time as a decimal because it is easier, but I want Excel to convert or substitute THE CELL RESULT into the real time format. Thus, i want the cell to show when i enter 12.30, i want the cell result to show as 12:30:00PM.
Secondly, I am calculating how many hours owed to me or by me to my employer and have a running balance column of "Time In Lieu Hours".
These are also expressed in Decimal. Thus 1.85 hours is saying one point eight five hours. I want this to show as TIME in Hours minutes format, but sometimes it might be a negative, thus 1.85 might be 1.85.
Is there a common format i can use in the Time In Lieu column that will accomodate the accrued hours if its a negative value.
Thanks for your help to both of these problems in advance.
Have an awesome day.
Regards
Craig
Hi, I am building a spreadsheet that totals an employees log in time to a phone system over a month. This needs to be compared to a number of hours worked, which comes in a decimal format (7.5 hours etc)
I have used TEXT($G4/24, "h:mm:ss") to convert the working hours into a time, however any time over 24 hours is not counted, for instance 25.5 decimal hours becomes 1:30:00 rather than 25:30:00 which would be required.
Does anybody know what kind of formula\formatting would achieve this?
Cheers!
Hello,
I have a Column in Excel that displays time in hh then fraction of hours..
eg.
Code:
Time Open 1
64.50
58.84
356.12
27.22
12.01
01.84
So the first entry is 64 hours and .50 of an hours (30 minutes)
The next one is 58 hours and .84 of an hour (about 50 minutes)
I'm trying to put in a formula to change this into the correct time format for [h]:mm
So far, I've delimited the decimal, then did the formula B2/100*60 which would convert the fraction into seconds, then rounddown the result. Then I would concatenate the hh and the mm with a : in the middle, and format the cell to [h]:mm.. But this doesn't really work..
Any ideas?
Thanks in Advance.
G'Day All,
I have a total column holding time worked as hours:minutes; I have a need to divide this figure by 7 hours 30 minutes to give me the total number of equivalent days (rounded up to the next whole day) recorded.
I have tried, with my limited excel expertise, various divisional methods but with no correct result.
Any help would be appreciated.
Regards
John
I am trying to enter a time of 33:13:00 and get a display of a decimal of
33.21. If I format the box to be decimal when I enter the time it does not
give the full 33 hours but in turn replaces it with 1.38. Is there are
function or formula that will allow the user to type in the time in hours and
minutes to convert it to a decimal format?
hi jsut a quick easy one that my brain wont process today.
I simply a time eg 00:05:25 that I want to divide by a number eg 6 to get the average time for the 6 instances. should be 50 somethign seconds. Im getting the result 4:00:54.
5 minutes divided by 6 isnt 4 hours.
thanks in advance for your help
(originally was trying to use the average function in a pivot table but was getting the same result, so thought id try and do it outside the table after it was created, but having no more luck, would prefer to do it in the pivot table if you have an answer for that instead)
cheers
There is probably a simple solution to my problem that I am just not seeing because I cannot believe I'm the only one trying to pull out the number of hours after summing a column of times when the total exceeds 24 hrs. I have attached a sample spreadsheet which has columns for start time, end time and time used (ie end  start time) Originally when I totalled the column of time used, I got 20:27 hrs instead of the correct 44:27 hrs. After doing some research I found out that I had to create a custom format of [h]:mm to get it show beyond 24 hrs. However, now that I can see the 44 hrs and 27 min as 44:27, I cannot seem to pull out the 44 hrs to use it in a calculation of multiplying total time against a charging rate ($/hr) to get a total cost in $. The sum using [h]:mm is in cell F68 and I was hoping to convert this [h]:mm into a number of hrs as a decimal (ie 44:27 to 44.45 hr) by =((HOUR(F68)+MINUTE(F68)/60)) but I get back to the 20 hrs of a h:mm format.
I was originally surprised that I couldn't sum a column of times and get the total time without creating a special format. But I am really surprised that it isn't intuitive to pull out the correct number of hours when it exceeds 24 hrs. I am sure that it's simple because it seems something that a lot of people would have going on in accounting type of spreadsheets. I am going to be doing a lot of summing times coming up and would appreciate suggestions. This forum helped me once before and I hope for the same results!
Thanks!
I have a billing sheet with the following headings: start time, end time, hours, billing rate, and amount.
I want the hours column to be (1) rounded up to the nearest tenth of an hour and (2) be formatted in decimal format to two places. For example, 13 minutes would be 0.30 hours. I also want the hours column to accommodate the situation where the end time is on a different day.
I have worked on this for 4 hours and cannot come up with a solution. I have looked for solutions all over Google. I do not think this is possible.
I'm temporarily stumped.
I am trying to create a little spreadsheet where I can calculate the end date and time of ebay auctions in my local time.
In one cell I have my local date & time with "=NOW()" and formatted as custom "dd/mm/yyyy hh:mm"
Ebay tells me the number of days and hours till the end of auction (eg 3 days, 19 hours =91 hours), so I would like to enter this in the cell below, and then a simple "=sum()" formula in the cell below, also formatted as custom "dd/mm/yyyy hh:mm" to get my local time and date of the end of auction.
The problem starts with the fact that I can't seem to enter a value of more then 23:59 as time format. Probably that is also the reason I can't get the result formula to work.
How do I go about getting this working?
NB.
I should also add that I work with Excel2003
MS Excel 2003
I am creating a timesheet that is based on 8hour days. The first column is labeled "Total Hours" which represents the total hours someone works. My spreadsheet has VBA code that convert numbers to time notation.
I also have a formula in each row (=IF(L8=0,SUM(C8:D8),(IF(L8=0,0,IF(SUM(C8:J8,M8)=0,8,L8+C8+D8SUM(E8:J8)+M8))))).
If there are no entries in range C8:J8,M8, the result should be 8:00 vs. 192:00. If I divide the aforementioned formula by 24, the result is a blank cell vs. 8:00. However, an entry of: 30 in cell F8, for example, displays the proper result (7:30 total hours).
What do I need to do so that the total hours for the day show 8:00? The format is [h]: mm.
I have a cell that displays, '8:32:00 AM', meaning 8 days, 32 hours and 0 minutes, which tecnically isn't correct. It should display '9:08:00', 9 days, 8 hours and 0 minutes, but that's how it was given to me. And I want to convert this to total number of minutes. I used the convert function, =CONVERT(H37,"hr",mn") (and I also did just a custom format as [m]), however I'm having a hard time trying to split the time out so that it reads the 8 days as 15520 minutes and the 32 (one day and 8 hours) as 1920 minutes. I think I should be writing a formula that contains either LEFT or LEN, or something like that but I'm having a real hard time trying to wrap my brain around this...
I someone could write the formula or offer a suggestionm, I would really apprciate it!!
Thank you!
~Darg
I'm having a problem trying to get the decimal of a number to convert(may not be the correct term) to quarters. What I have is a time sheet that my office is wanting to automate in Excel. It must contain 24hr time entries which I have the cells formated to 00\:00 because they didn't want to have to enter the colon. They want to be able to just enter 1030 and have it display as 10:30. At the end of the day there is a cell for the Total Hours Worked and is formatted as 0\:00. There is another section that reports time taken off in hours like 4 or 2.5 and this is formatted as a number with two decimal points.
Since the time format is 00\:00, when 10:00 is entered excel reads it as a number 1000. This is no problem except when it totals the time for that day. The Total Hours Worked for the day is calculated as Ending time minus Starting time. Example, if an employee works from 8:00 until 10:30, the formula is [1030  800 = 230] and is displayed as 2.3 [format: 0\:00]. This is correct, the employee did indeed work two hours and thirty minuets however, I need for it to be displayed as 2.5 hours. Because minutes, for time keeping purposes, are rounded to quarter hours I need is some way to convert(again, may not be the correct term) the decimals in the following order:
.1  .25 = .15
.26  .50 = .5
.51  .75 = .45
.76  .99 = 1.0
I am not very well versed in VB so if there is a nonVB solution to this it would be preferred.
Hi all,
Complex situation i have work out the dockets that have caused more than a set target percentage of time that will cause the overall target to fail. (confussed i was)
i have a calculation based on the hours in a month to work out the percentage of time applicable e.g.
a1 = 0.95
a2 = 31 (days in the month)
a3 = a2*24 (hours in the month)
a4 = a1*a3 (95% of the hours in a month)
a5 = a3a4 (hours that can be counted before a failure based on the percentage)
= 37.2 (decimal hours) "Z"
so for each docket i have to calc the hours and minutes the docket took to complete which i have done "start date and time"  "end date and time" format result as [hh]:mm "X"
so using the above calc and the result for each docket i need to know how many dockets have a time greater than the answer. "X" > "Z"
I have one docket with a time formatted at 50:00 so when i do a sumproduct for certain dockets that have a value greater than "Z" i should get a value of 1 but i dont
docket data is laid out as
b2 docket ID (1100)
c2 start date time per row
d2 end date time per row
e2 = diff between c2 and d2 [hh]:mm
is this becasue im mixing formats ?
Hello. I've got a spreadsheet that calculates the time I've worked for the
two weeks of each pay period and approximate pay for those weeks. However, I
just came across the goal seeker function and it gave me this great idea, I
want to be able to put in the time I clocked in, the time I clocked out for
lunch, and the time I clocked back in from lunch, and have the computer tell
me what time my 8 hours are up. (I'm not allowed to go over 40 in a week, but
my schedule is flexible in that I can arrive early and take short lunches and
leave early, etc.) I know I can goal seek this but I want to have a formula
to do it for me so I don't have to. I'm currently entering the time in in
military time, so I don't know if that makes a difference or not.
Afternoon all,
I was messing around with a simple countdown timer and need a little help converting a decimal into a format of #days, #hours, #minutes and #seconds.
Cell A1 has target to countdown to 4/14/2010 12:00:00 AM
Cell A2 contains =NOW()
Cell A3 contains =A1A2 with the result being 69.4021441 with the amount of decimal places varying depending on when it is refreshed.
I'd Like the result in A3 to read something like "There are 69 days, XX hours, XX minutes and XX seconds until event"
The help is always appreciated
I've created a weekly rota for my barstaff in excel 2003 and need some help to finish it off as I would like it.
It's a basic format where it will show name, start time, end time and number of hours worked for each member of staff for each day. So for example in the Sunday column cell B4 shows "Chloe", cell B5 shows "1100" (start time), cell C5 shows "1800" and cell C4 shows "7" (hours worked that day).
Obviously there are many members of staff doing different shifts on different days and this will change from week to week so what I'm trying to do is tally the total hours each staff member has worked by the end of the week. Is it possible for me to search for a staff member's name and tally the figures in the adjacent cells each time it is found?
I can attach my current file if this helps. Many thanks in advance.

