Finding time intervals in excel

**phbryan** · 12-18-2011, 10:15 AM

Hello,

I'm trying to create a spreadsheet for a workweek. The program will need to calculate the total number of hours worked in a given day, determine if it is greater or less than 6 hours, if greater subtract 30 minutes for a break, if less do nothing, and return a total number of hours worked in number format.

For example, a user will input an employee as working 8am - 5pm. The program then should output 8.5. Then, add all hours together for a week and output total hours working in number format. Example, 20.5.

I'm using excel 2007, but i also need it to work in earlier versions such as excel 2001.

Thanks

**arthurbr** · 12-18-2011, 11:25 AM

Hi and welcome to the board

Perhaps

 =if((end_time-start_time)<6/24,(end_time-start_time),(end_time-start_time)-1/48)

( keeps time as sexagesimal)

Sum the daily results and multiply the sum by 24 - Format as number

**phbryan** · 12-18-2011, 11:40 AM

Originally Posted by arthurbr

Hi and welcome to the board

Perhaps

 =if((end_time-start_time)<6/24,(end_time-start_time),(end_time-start_time)-1/48)

( keeps time as sexagesimal)

Sum the daily results and multiply the sum by 24 - Format as number

Thanks this is a lot cleaner then the way i got to do it. Now i'm setting up a weekly schedule spreadsheet. Suppose an employee does not work a day and i want to put an "X" instead. For example, i have 8:00 AM - 2:00 PM in three cells (cell 1: 8:00 am, cell 2: -, cell 3: 2:00). If a person dosent work then i wanna put in "x" or maybe x-x. How will this affect the formula, i basically want it to be omitted from all calculations and the and the sum still be returned for other days.

**arthurbr** · 12-18-2011, 12:30 PM

1. Please do not quote entire posts - I wonder in which color it should be written so that posters see the request in my sig !
2, Using the range form of the sum function ( i.e. SUM(A1:B5) ) does not " see" cells containng text, so, np.

**phbryan** · 12-18-2011, 02:06 PM

The problem i am having is that im trying to sum up the results of 7 different if statements for 7 different days. If one of the days is left blank, then the if statement returns zero and there is no problem, but for placeholder purposes i want to put an X. When this happens the if statement for this day returns "#VALUE!" and as a result makes the sum of the entire week return #VALUE!

**arthurbr** · 12-18-2011, 03:10 PM

You can amend as follows :

=IF(ISERROR(D2-B2),"",(IF((D2-B2)<6/24,(D2-B2),(D2-B2)-1/48)))

**phbryan** · 12-18-2011, 05:40 PM

Awesome this works perfectly!

One final question, how can i format a time cell to display just the first leading digit. For example, 8:00am - 4:30 pm i would like to be displayed as follows, 8-4:30, to save space.

If this is not possible then is there a formula to convert time to a number as follows: 8:00 am or pm to 8, and 4:30 am or pm to 4:30.

**arthurbr** · 12-19-2011, 02:20 AM

I would suggest formatting your time in the 24-hour system instead of the 12 hr or custom format your cells as hh instead of hh:mm. The underlying value will not change ( but is that really a good idea?)

To convert time to a decimal number, multiply by 24 and format as number ( 4:30 will become 4.5 of course)