I'm Having A Problem With Time Formula

Drip_Drip

Board Regular
Joined
Oct 15, 2004
Messages
54
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

Thanks in advance.
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Mar 23, 2005
Messages
20,825
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top