Calculating times when the end time could be before the start time

**Miko K** · 10-19-2011, 11:26 PM

I am so lost and I have read so many forums. Is anybody able to help me?

Basically, I am tracking the performance of a live production line. New dates and times are sent out twice a day every day. I need to track if the new loading times are going forward or backward. Of course the forward times are easy, I've read many forums but can't seem to narrow down how to read the negative value in hours as shown. The cell will need to read either the positive or negative value depending on the next updated schedule sent out. Please see attached picture for reference.

Thanks so much

**MarvinP** · 10-20-2011, 12:23 AM

Hi Miko k and welcome to the forum,

You can't display times that are negative, as you've discovered. But how about conditional formatting them to red of they should be neg? See the attached.

**Miko K** · 10-20-2011, 09:08 AM

Originally Posted by MarvinP

Hi Miko k and welcome to the forum,

You can't display times that are negative, as you've discovered. But how about conditional formatting them to red of they should be neg? See the attached.

Hi thanks for looking at my problem.

It looks a bit there, but it is only counting the hours difference. If the line is say 25 hours behind, the calculation is only showing 1 hour behind.

**MarvinP** · 10-20-2011, 10:07 AM

Hi,
Look at
http://www.ozgrid.com/forum/showthread.php?t=25985 or
http://www.excelforum.com/excel-gene...s-minutes.html

**daddylonglegs** · 10-20-2011, 10:22 AM

You can display negative times if you use "1904 date system". You need to set that in a workbook before you put the dates in....otherwise the dates will change.....so in a blank workbook click on "Office" button at top left then

Excel Options > Advanced > scroll down to "When calculating this workbook" > tick "1904 date system"

Now copy in your dates....and you can use a simple subtraction formula, e.g. in C1

=B1-A1

custom format C1 as

[h]:mm;[Red]([h]:mm);h:mm

to show as required

**Miko K** · 10-20-2011, 12:20 PM

Originally Posted by MarvinP

Hi,
Look at
http://www.ozgrid.com/forum/showthread.php?t=25985 or
http://www.excelforum.com/excel-gene...s-minutes.html

Hi Sorry the first one still calcualtes out over 40,835 days and the second one isn't really what I need. Guess I have picked an interesting one to tackle.

**Miko K** · 10-20-2011, 12:37 PM

Originally Posted by daddylonglegs

You can display negative times if you use "1904 date system". You need to set that in a workbook before you put the dates in....otherwise the dates will change.....so in a blank workbook click on "Office" button at top left then

Excel Options > Advanced > scroll down to "When calculating this workbook" > tick "1904 date system"

Now copy in your dates....and you can use a simple subtraction formula, e.g. in C1

=B1-A1

custom format C1 as

[h]:mm;[Red]([h]:mm);h:mm

to show as required

Hi, I've tried the 1904 system, however it was converting all my dates to the year 2015 once I dump them in. But with a bit of format retweaking ideas from this forum and the year not actually being a necessary part of my needs, this would actually work out pretty good.. but once Feb 29 2012 hits, it adds one day to each formula. Ah the stress.

**daddylonglegs** · 10-20-2011, 01:43 PM

OK, Yes that's a good point. You can convert the dates back to the correct ones. Put this number in a cell

1461

[that's the number of days that the dates change by]

Copy that cell then select your range of dates > right click > Paste Special > Subtract > OK > ESC