# What is the formula to show the negative value during computing the time variance?

#### Vinosh

##### New Member
Case is this:

Each task is estimated certain number of hours to complete. The actual number of hours consumed to complete the task is calculated.

Now I want to find out the variance between estimated time and actual time. I get #### when variance is negative value.

 Estimate Actual Variance +/- 2:00:00 8:00:00 ########## 4:00:00 8:30:00 ########## 8:00:00 6:00:00 2:00:00 16:00:00 3:00:00 13:00:00 32:00:00 1:00:00 31:00:00

<colgroup><col span="3"></colgroup><tbody>
</tbody>

What is the formula to calculate variance and to show the negative value in hours?

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Caribeiro77

##### Well-known Member
If you don't need the variance column to future calculations you can use:

=IF(B1>A1,B1-A1,TEXT(MAX(A1:B1)-MIN(A1:B1),"-h:mm:ss"))

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
If you don't need the variance column to future calculations you can use:

=IF(B1>A1,B1-A1,TEXT(MAX(A1:B1)-MIN(A1:B1),"-h:mm:ss"))
That will show all variances as negative, not just the first two in the sample data.

Excel Workbook
ABC
1EstimateActualVariance +/-
22:00:008:00:00-6:00:00
34:00:008:30:00-4:30:00
48:00:006:00:002:00:00
516:00:003:00:0013:00:00
632:00:001:00:007:00:00
Variance

Another option is to change Excel's default date system for this workbook to the 1904 date system and negative time should display correctly with a simple subtraction:

Excel Workbook
ABC
1EstimateActualVariance +/-
22:00:008:00:00-6:00:00
34:00:008:30:00-4:30:00
48:00:006:00:002:00:00
516:00:003:00:0013:00:00
632:00:001:00:007:00:00
Variance (2)

Last edited:

#### Vinosh

##### New Member
That resolves my issue.
What is the problem with this formula if I have to use the result of variance in future calculations?

In fact, I have about 3000+ such odd tasks for which I need to find the variance between estimated time and actual consumed time. I will have to validate the difference on a regular period, say weekly/fortnightly etc, to take action on the variance, so that the work progresses smoothly.

Will I have a any issue in this if use the given formula?

#### Peter_SSs

##### MrExcel MVP, Moderator
Firstly, notice that the result in row 6 of each of my previous screen shots appears incorrect.
In the first one, the TEXT formula needs adjustment - see corrected formula in the first screen shot below.
In the second one, the cells need to have a Custom format applied: [h]:mm:ss so that the results appear as shown in the second screen shot below.

What is the problem with this formula if I have to use the result of variance in future calculations?
Well, the problem is if you want to do some further arithmetic calculations with these results. Since the =TEXT.. formula does not return numbers, you will have a problem.

For example, suppose that you want to find the total (SUM) of all the variances, or the average variance, look what happens in cells C7 and C8 below. Neither formula gives the correct results.

Excel Workbook
ABC
1EstimateActualVariance +/-
22:00:008:00:00-6:00:00
34:00:008:30:00-4:30:00
48:00:006:00:002:00:00
516:00:003:00:0013:00:00
632:00:001:00:0031:00:00
7Total0:00:00
8Average#DIV/0!
Variance

However, with the change in Date system method, these additional calculations work as expected.

Excel Workbook
ABC
1EstimateActualVariance +/-
22:00:008:00:00-6:00:00
34:00:008:30:00-4:30:00
48:00:006:00:002:00:00
516:00:003:00:0013:00:00
632:00:001:00:0031:00:00
7Total35:30:00
8Average7:06:00
Variance (2)

#### Vinosh

##### New Member
That is informative. I understand the point. Thanks a lot.

#### Peter_SSs

##### MrExcel MVP, Moderator
That is informative. I understand the point. Thanks a lot.

Replies
1
Views
306
Replies
5
Views
220
Replies
1
Views
230
Replies
2
Views
301
Replies
1
Views
191

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

### 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