# I'm Having A Problem With Time Formula

#### Drip_Drip

##### Board Regular
I'm using the formula shown below in cell K5. If I drag the formula down it works correctly except cell K6 which shows 23:15 rather than 00:45. Can anyone assist so that the correct values are shown?

Please note that the values are based on hours and minutes which are formatted as [hh]:mm

Ideally I would like to show a reduction with a minus sign but this is not essential as I can use some conditional formatting to get around this.

Column E Column H Column K (Result)

842:31 832:25 10:06
501:51 502:36 23:15 (Incorrect)
832:25 502:36 329:49
502:36 501:51 00:45

Code:
``=IF(E5<H5,E5-H5+(E5<H5),E5-H5)``
Code:

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Scott Huish

##### MrExcel MVP
The easiest way would be to use the 1904 date system, but this will affect any dates you currently have on the sheet, then
=H1-E1

if you want to use Conditional Formatting to show the negatives, then perhaps:
=ABS(H1-E1)

and a conditional formatting formula of:
=\$E1>\$H1

#### barry houdini

##### MrExcel MVP
That sort of formula is used for calculating time differences when you have times between 00:00 and 24:00, it won't work here.

Excel can show negative times if you switch to 1904 date system

Tools > Options > Calculation > 1904 date system

but this will change any dates you have in your spreadsheet currently.

Using that you can just use

=E5-H5

or without changing date systems you could try

=IF(E5< H5,TEXT(H5-E5,"-[h]:mm"),E5-H5)

Note: that the negative values will be text strings that you can't easily use in other calculations

Replies
6
Views
356
Replies
1
Views
113
Replies
3
Views
3K
Replies
1
Views
557
Replies
6
Views
707

1,181,371
Messages
5,929,561
Members
436,677
Latest member
CathalP1992

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back