## Count complete hours between two times

The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5.

Formula in cell D5:

Note that this formula works only with time entries, not date and time entries. It doesn't matter if the start time is past the end time, the formula will regardless return a positive hour value.

### Explaining formula in cell D5

#### Step 1 - Subtract time entries

C5-B5

becomes

6:00:00 AM - 12:00:00 AM

Time entries in excel are actually a decimal value between 0 (zero) and 1. One hour is 1/24, six hours is 6/24 and so on.

0.25 - 0 equals 0.25

#### Step 2 - Remove sign

The ABS function removes the minus sign if it exists, the HOUR function can't calculate negative values.

ABS(C5-B5)

becomes

ABS(0.25) and returns 0.25.

#### Step 3 - Convert decimal to hour(s)

The HOUR function calculates hours based on a decimal value.

HOUR(ABS(C5-B5))

becomes

HOUR(0.25)

and returns 6.

The image above demonstrates a formula the returns negative hours if the start time is later than the end time.

Formula in cell D5:

### Explaining formula in cell D5

This part of the formula HOUR(ABS(C5-B5)) is explained above.

#### Step 1 - Calculate if end date is smaller than the start date

C5<B5

becomes

0.25<0

and returns FALSE.

#### Step 2 - Return -1 if TRUE and 1 if FALSE

IF(C5<B5,-1,1)

becomes

IF(0.25<0,-1,1)

becomes

IF(FALSE,-1,1) and returns 1.

#### Step 3 - Multiply with result

HOUR(ABS(C5-B5))*IF(C5<B5,-1,1)

becomes

6*IF(C5<B5,-1,1)

becomes

6*1 and returns 6 in cell D5.

The picture above shows a formula that calculates complete hours between date and time entries.

Formula in cell D5:

If you want negative hours then remove the ABS function:

The TEXT function formats the result of C5-B5 into complete hours.

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, [โฆ]

Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes [โฆ]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form