Dividing Time 


Dividing Time  Excel 
View Answers 
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.
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 Tutorials
Increment a Value Every X Number of Rows in Excel
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial teaches you how to build ...
Join a Date and Time into a Single Cell using VBA Macros in Excel
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
NOW()  Display The Current Time in Excel
This Excel tip will show you how to display the current time within any Excel spreadsheet. This is a useful functio ...
This Excel tip will show you how to display the current time within any Excel spreadsheet. This is a useful functio ...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
Helpful Excel Macros
Automatically Run a Macro at a Certain Time  i.e. Run a Macro at 4:30PM every day
 Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
 Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
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
 This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
 Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
 Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Format Cells in The Number (Numerical) Number Format in Excel
 This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
 This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Format Cells as an Accounting Number in Excel Number Formatting
 This free Excel macro formats a selected cell as an Accounting number. This means that the accounting number format is
 This free Excel macro formats a selected cell as an Accounting number. This means that the accounting number format is
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
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?
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
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
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,
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
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
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
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
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 all,
I am trying to total the hours each staff member has worked in a week.
The hours for each day are in cells formatted in custom hh:mm. When I calculate the weekly total I get the result in time format. So if a staff member has worked 25 hours the cell returns 01:00. I'm assuming I need to be using a different cell format just not sure which one.
Thanks in advance,
Luke
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!
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:
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.
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 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?
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?
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 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!
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
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
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 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
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 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.
Hello everybody,
If anyone could help with a solution, I would be very grateful.
Setting 1
Time is 24hr clock
Checkin time A1 e.g. 07:41
Checkout time B1 e.g. 23:15
Both cells are in GENERAL format (but also can be CUSTOM format, h:mm:ss )
Setting 2
Time is 24hr clock
Checkin time A1 e.g. 19:58
Checkout time B1 e.g. 04:09
Both cells are in GENERAL format (but also can be CUSTOM format, h:mm:ss )
Objective
Calculate hours worked for setting 1
=(B1A1) which calculates to 15:34:00 (store in C1)
Calculate hours for setting 2
=(24A2+B2) which calculates to 8:11:00 (store in C2)
Now in setting 1,
We want to calculate hourly rate and also if minutes worked is greater than 30mins
we add 30mins to the hours and multiply by hourly rate.
So for setting 1, staff hours amount to 15 hours plus 30mins
In setting 2, for minutes part of calculation, minutes are 11 mins and are less than the
30min condition so 30mins is not added to the total hours worked.
So for setting 2, staff hours amount to 8 hours only
I have been trying to compare the minutes part of the time worked by staff against the 30min
condition in order to decicde if 30mins work time should be added to the hours worked or not.
I have tried using the IF and TIME functions to produce this condition but so far havenot been able to solve this issue.
For example to store in D1 whether staff work hours to include 30mins or not
=IF(TIME(0,C1,0)>=TIME(0,30,0),TIME(C1,0,0)+0:30:00, TIME(C1,0,0))
In setting 1, the resulting total hours worked should be 15hrs 30mins
in settijg 2, the resulting total hours worked should be 8hrs only
I have tried a number if combinations long the lines above but I think I am confusing time in hours and minutes with time as a decimal or mixing formats wrongly.
Any advice would be greatly appreciated.
Best Regards
Naylordaisy
Hi All,
I am new to the Excel Forum and wonder if any help can be given for my first problem? I am trying to create a formula for a timesheet that will convert total hours worked into days or half day. I can create a formula that will sum up hours if entered in the correct format but would now like to convert this in to either a 0.5 or 1 type result for half day or full day worked.
My current formula is very basic with cells in a custom hh:mm format and an example is as follows =SUM($D40$C40$E40+$F40)$F40, but it works for what we need. How would I then sort this to do the above?
Any help would be appreciated, if you require further info then please ask.
Thanks
Andy
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 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.
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 ?
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 ?