| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hours and sum of hours
I want to create a spreadsheet in which I record total number of hours spend
on a maintenance job whereby minutes less then 30 are rounded to half an hour and more then 30 are rounded to the hour. Eg : 5:30 should be displayed as 5.5; 2:15 should become 2.5. 4:45 should be come 5.0. Which function should I use to facilate entering the data? Next I want to sum the total number of hours and minutes. Obviously, if the sum of minutes if greater then 60 the number of hours should be increased. Example : 15 hours and 90 minutes should become 16.5 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hours and sum of hours
Try the below formul and format to general.
=ROUND(A1*24/0.5,0)*0.5 If this post helps click Yes --------------- Jacob Skaria "u650431" wrote: I want to create a spreadsheet in which I record total number of hours spend on a maintenance job whereby minutes less then 30 are rounded to half an hour and more then 30 are rounded to the hour. Eg : 5:30 should be displayed as 5.5; 2:15 should become 2.5. 4:45 should be come 5.0. Which function should I use to facilate entering the data? Next I want to sum the total number of hours and minutes. Obviously, if the sum of minutes if greater then 60 the number of hours should be increased. Example : 15 hours and 90 minutes should become 16.5 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hours and sum of hours
For you second part; with the hours in A1:A10 try the below formula
=ROUND(SUM(A1:A10)*24/0.5,0)*0.5 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below formul and format to general. =ROUND(A1*24/0.5,0)*0.5 If this post helps click Yes --------------- Jacob Skaria "u650431" wrote: I want to create a spreadsheet in which I record total number of hours spend on a maintenance job whereby minutes less then 30 are rounded to half an hour and more then 30 are rounded to the hour. Eg : 5:30 should be displayed as 5.5; 2:15 should become 2.5. 4:45 should be come 5.0. Which function should I use to facilate entering the data? Next I want to sum the total number of hours and minutes. Obviously, if the sum of minutes if greater then 60 the number of hours should be increased. Example : 15 hours and 90 minutes should become 16.5 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif and translate annual hours into weekly quarter hours | Excel Worksheet Functions | |||
| wages - multiply hours and minutes by number of hours worked | Excel Discussion (Misc queries) | |||
| Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
| how to add hours and show actual hours not decimal numbers | New Users to Excel | |||
| Convert hours and minutes in time format into fractions of hours.. | Excel Worksheet Functions | |||
Linear Mode
