Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating decimals
On our timesheets we must enter quarter hours as .15, .30, .45. Is there a
way to enter these decimals but have excel calculate them as .25, .50, .75. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating decimals
Suppose A1 and A1 have you 'funny' times such as 2.15 and 3.45 The formula =TIME(INT(A2),MOD(A2,1)*100,0)-TIME(INT(A1),MOD(A1,1)*100,0)+(A1A2) will return the difference (in hours and mins). The last term allows for A2 being the next day and numerically less than A1. You will need to format the cell as [h]:mm best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Headacheaday" wrote in message ... On our timesheets we must enter quarter hours as .15, .30, .45. Is there a way to enter these decimals but have excel calculate them as .25, .50, .75. Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating decimals
Hi
Enter times as times. I.e. 8:15 or 16:45, etc. Then to calculate any time interval you'll have ordinary substraction, like =B2-A2, and cell with formula formatte as time too. To use any time or time interval in calculations as number of hours, you have to multiply it with 24 (24 hours equals to integer value 1). I.e when you have p.e. entered into a2 a time value 3:15 , then 24*A2=3.25 (to see the result this way, format the cell as general). When you have to calculate time intervals which include midnight rollover, use the formula like =B2-A2+(B2<A2) (NB! This formula is valid only for time itervals less than 24 hours). When you sum up times/time intervals in some cell, and the sum can exceede 24 hours, format the cell as "[h]:mm". Arvi Laanemets "Headacheaday" wrote in message ... On our timesheets we must enter quarter hours as .15, .30, .45. Is there a way to enter these decimals but have excel calculate them as .25, .50, .75. Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating decimals
Try this:
=TIME(INT(A1),MOD(A1,1)*100,0)*24 A1 = 7.15 Formula returns: 7.25 Biff "Headacheaday" wrote in message ... On our timesheets we must enter quarter hours as .15, .30, .45. Is there a way to enter these decimals but have excel calculate them as .25, .50, .75. Thanks, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating decimals
For a comprehensive discussion about using Excel for timesheets, see
http://www.cpearson.com/excel/overtime.htm "Headacheaday" wrote: On our timesheets we must enter quarter hours as .15, .30, .45. Is there a way to enter these decimals but have excel calculate them as .25, .50, .75. Thanks, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating decimals
Biff.. thanks it worked great. Now I need to add an entire column, of numbers.
A1:A45 Is there a way to do this without having to enter the formula you gave me for each cell in the total cell. i.e. =TIME(INT(A1),MOD(A1,1)*100,0)*24 then I need to do the same thing for cells A2 through A45. That would be a very long formula. Thanks again for all your help. "Headacheaday" wrote: On our timesheets we must enter quarter hours as .15, .30, .45. Is there a way to enter these decimals but have excel calculate them as .25, .50, .75. Thanks, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating decimals
If you've got your formula in B1 referring to A1, & your source data in A1
to A45, you can select B1 and then select the bottom right-hand corner of cell B1 (where you'll see a little black square) & drag down through B2 to B45, or double-click that bottom right-hand corner of B1 and it will auto-fill down to B45. -- David Biddulph "Headacheaday" wrote in message ... Biff.. thanks it worked great. Now I need to add an entire column, of numbers. A1:A45 Is there a way to do this without having to enter the formula you gave me for each cell in the total cell. i.e. =TIME(INT(A1),MOD(A1,1)*100,0)*24 then I need to do the same thing for cells A2 through A45. That would be a very long formula. Thanks again for all your help. "Headacheaday" wrote: On our timesheets we must enter quarter hours as .15, .30, .45. Is there a way to enter these decimals but have excel calculate them as .25, .50, .75. Thanks, |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating decimals
Try this:
=SUMPRODUCT(TIME(INT(A1:A45),MOD(A1:A45,1)*100,0)* 24) Biff "Headacheaday" wrote in message ... Biff.. thanks it worked great. Now I need to add an entire column, of numbers. A1:A45 Is there a way to do this without having to enter the formula you gave me for each cell in the total cell. i.e. =TIME(INT(A1),MOD(A1,1)*100,0)*24 then I need to do the same thing for cells A2 through A45. That would be a very long formula. Thanks again for all your help. "Headacheaday" wrote: On our timesheets we must enter quarter hours as .15, .30, .45. Is there a way to enter these decimals but have excel calculate them as .25, .50, .75. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying a number with different decimals depending on a condition | Excel Worksheet Functions | |||
Easy calculating ending with wrong decimals | Excel Discussion (Misc queries) | |||
How do I calculate without decimals in excel? | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) |