Time duration in Excel 2007

Topic

New Reply

In calculating time durations some results might be less than six hours while others can be greater than 6 hours. How can I express those duration that are equal or greater than 6 hours long by subtracting 30 mins of time from the total using an “=IF “ statement? Or is there an another method?
Example:
A2=9:00 am B2= 1:00 PM resulting in a total of 4:00 hrs in C2 (B2-A2). However the next entry may be
A3=8:00 am B3=4:00 PM resulting in a total of 8:00 hrs in C3 (B3-A3). Since it’s more than 6 hrs I want to deduct 30 minutes to show 7hrs 30 min.
Bear in mind the times can be anything from 1 to 15 hours long for any entry.

Any suggestions will be greatly appreciated.

Reply | Quote

Replies

=B2-A2-((B2-A2)>6/24)*0.5/24

Or with one of the variations with an if:
=B2-A2-IF((B2-A2)>6/24,0.5,0)/24

Note: 6/24 is 6 hours and 0.5/24 is a half-hour since time in excel is in units of DAYS.

Steve

Reply | Quote

Attached is another example

Reply | Quote

I like your layout and it gave me several other ideas on how to present my data.

Reply | Quote

=B2-A2-((B2-A2)>6/24)*0.5/24

Or with one of the variations with an if:
=B2-A2-IF((B2-A2)>6/24,0.5,0)/24

Note: 6/24 is 6 hours and 0.5/24 is a half-hour since time in excel is in units of DAYS.

Steve

Steve, Thank you. The key is that I did not understand or realize the 6/24 and .5/24.

Reply | Quote

The key is that I did not understand or realize the 6/24 and .5/24

This is a key point in understanding dates and times in Excel. Dates and times in excel are all based on units of DAYS, so the integer portion of a date/time defines the day (the number of days past 1/1/1900). The decimal portion of the number is the fraction of a day. 0.25 = (6/24) is 6 AM, 0.5 (12/24) is noon, 0/75 (18/24) is 6PM, etc

This system allows subtraction to determine the number of days between 2 date/times. This allows using the builtin excel date/time functions. Or if you want to use decimal numbers, you can multiply the difference by 24 to get hours, by 24*60 to get minutes, etc.

Steve

Reply | Quote

Since I don’t use the time functions very often I know I won’t remember any of this in the near future. I’m going to save the replies for future reference.

Thanks again to you and the others who posted.

Reply | Quote

Is there a way to add seconds to be calculated by the formula as well?

I am trying to calculate the duration between start and end time of running some reports.

Reply | Quote