# Dealing With Negative Time Formatting

Jul 16, 2007

It seems that time (i.e. -1:00) will be default as #########, etc. This makes me very unhappy. How to get around?

I could be fine with converting time to a total in seconds (i.e. 1:00 converted to 60 seconds)... but I'm not sure what kind of formula could do that.

ADVERTISEMENT

## Dealing With Negative Numbers In Nested If

Jan 3, 2013

I don't know how to manipulate the formula with quoting the "You can't have cups less than zero".

Teletubbies coffee - Nested If

Create a nested If function to describe the Teletubbies coffee drinking habits based on the following criteria:

0 cups = Tea drinker

1-5 cups = Normal

More than 5 cups = Caffeine fiend

Copy the function down and check that it works.

How to structure the If function

Try modifying the If function so that if Cups of coffee is a negative number you see an appropriate error message. Make sure the rest of the function still works!

## Conditional Formatting Negative Time

Apr 24, 2009

Excel 2000. I am having a little problem getting the list of numbers detailed below to turn red if Negative and Green if positive, (0:00 to stay blank). These numbers will changed between a maximum of 120:00hrs and -120:00hrs....

## Negative Formatting ( ) Alignment

Sep 22, 2009

How to show negative values displayed in brackets so that it is aligned properly with positive values in the same column. That is, the right bracket of the negative value is as follows:

(1,234.56)
800.12 - so that 2 is aligned underneath 6

I have done research on the forum and help on vba and got the following
"#,##0.00_);[Red](\$#,##0.00)"

I understand that the underscore is needed for the alignment.
However, when I include the _) the above is shown as follows:

(1,234.56)
800.12_) the underscore and bracket are included and the bracket and is aligned underneath the right bracket above.

## Axis Formatting Negative Numbers

Apr 26, 2014

I am using the following format code for the y axis of a line chart. I am shortening the axis to show 3M or 500K instead of \$3,000,000 or \$500,000. I can't get it to work with negative numbers, I get the full \$3,000,000. Somewhere I read you can only do 3 formats in a formula. Is there a way to include negative numbers using this formatting?

[>999999]\$#,,”M”;[>999]\$#,”K”;\$#

## Formatting Numbers To Have Negative In Brackets?

Apr 2, 2014

I am currently using the following format to display numbers in my excel.

_(* #,###,###_);_(* (#,###,###);_(* "-"_);@

The brackets and underscores are used so that the positive and negative numbers align with overhanging brackets.

I want to modify the format such that it is able to display decimals where ever applicable.

For example

1,000 display as 1,000
0 display as a dash "-"
1.265 display as 1.265
-0.51 display as (0.51)

I tried changing it to:

_(* #,###,###.###_);_(* (#,###,###.###);_(* "-"_);@

However it added a "." to all positive and negative numbers regardless of whether there were decimals after it.

e.g.

10 displayed as 10.
-30 displayed as (30.)

In otherwords - I am trying to find the "general" format and modify it to include brackets for negative number, and also modify it so that the positive numbers aligning with the negative numbers with the ) over hanging.

## Negative Time

Jan 30, 2007

i am tracking my working hours at night, so i type in the time i start and the time i quit like this:

A 1 start B 1 end

A 2 22:30 B 2 02:30

now i want to calculate the time between.
but since excel don't like the negative time i got a problem. i figure i must make a function something like
=IF(B2<A1,B2+24)

i have tried a few but i don't get the parameters right so i get errors,

## Negative The Time

Mar 8, 2008

I have a formula calculating time to the second (e.g HH:MM:SS). I am calculating the difference from the time something was scheduled to be done vs. when it was actually completed. What I can't get Excel to do is calculate the same time but listing the time in negative, as if the project went over and by how much.

I know how to do this in numbers view, but unable to do this in time view.

## How To Make Conditional Formatting For Negative Numbers

Feb 19, 2014

I need the conditional formatting to make all numbers that are zero clear (i.e. no fill).

I need it to make all negative numbers to be red, however it doesn't seem to recognize "-1" as a number, and ends up highlight everything red when I say "highlight values < -1 red".

How would I do this?

## Currency Formatting Show A Negative Amount

Jan 19, 2010

When a user enters an amount in a cell, in �'s, i need it to show a negative amount. So if they enter �100 I want excel to regard it as -�100.

## Conditional Formatting With OR Statement And Negative Values

Dec 11, 2013

I am attempting to add conditional formatting (yellow fill) to cells that are greater than 15% or less than -15%. I've tried the following formula but, it highlights all cells.

=or(b2:b5>15%,b2:b5

## Account For �negative� Time

Dec 9, 2009

I�m trying to get the difference between times, BUT I want to account for �negative� time. So if I project that an employee will finish a task at 2 am, and they finished at 2:30 am, I want the result to be -:30, not just :30. If they finished at 1:30 then it would just be :30. Hence, my dilemma. I can get the difference, but didn't think Excel could recognize negative times. Right now I am using =MOD((A1-A2),1) or =(A1-A2+(A1<A2))*24/24 and in cells A1+A2 I am using military time.

## Negative Time Formula

Dec 22, 2009

I can't use 1904. I have a formula that almost works.

I am doing everything in Military time. This is the finial peice of the puzzle.

Our shift begins at 18:00 and ends up until 7:00 (am) or so depending onwork

A1 I have a time (Projected-Estimate of when all work should be done)
A2 I have a time (Actual- Time the work was actually complete.)
A3 Will be show the difference in either a Negative or Positive time.

Three Examples: (The first two work as is and are perfect) ....

## Display Negative Time?

Nov 14, 2008

how to do that? In "mm:ss" format if possible

## Time Of Day As Negative Decimal

Feb 6, 2012

Data from a SQL database represents time of day as a string of x's when imported into Excel. With CLEAN, I found that noon is -0.5.

This means that the calculation being used for noon is (24 * -0.5), or -12, meaning "go to the end of the 24 hour day, then go back half a day to noon." Similar calculations work other times of day.

Time entries that start in one day and end in the next are not allowed, so there is no problem with a change in day,

How can I convert this to the AM/PM time that my users need?

## Handling Time Goes To Negative Using VBA

Jun 11, 2009

My worksheet takes a time feed in the format '00:01:05' and I want to trigger an event when it goes to negative (eg, -00:01:05). But I have no idea how to handle it.

## Negative Time Subtraction

Jul 18, 2006

Ecel 2000

If Ii subtract time
eg 22:00-01:00 I get ############### wrong
If I subtract 22:00 23:00 I get 1 HR correct

## Conditional Formatting Highlights Same Number Even If Positive / Negative

May 29, 2013

Can I use Conditional formatting (highlights duplicate values) but highlight the number even if the number is an Positive or Negative number.

It must highlight the number if it's -300 or 300 in both instances.

## Negative Time Formula Variation

Dec 22, 2009

(First please don't send me a link on how to subtract time or show Negative time. I know about 1904 ect.. I can't use that. I need a variation on the formula I am using if possible)

A1 I have a time
A2 I have a time
A3 I have a formula subtracting A2 from A1

The times will vary in A1 & A2. Sometimes A1 will be earlier or later thus giving me negative or positive times. I have a formula that will give me a correct answer but will always show either Positive or Neg depending on what I put in the last part of the formula "-H:MM or H::MM" =TEXT(MAX(\$A\$1:\$A\$2)-MIN(\$A\$1:\$A\$2),"-H::MM") in A3. I want it to do the subtraction but say if A1 is > than A2 then "-H::mm", If A1 is < A2 then "h::mm". I don't know if that is possibly or how to incorporate that into what I have.

## Negative Time Or Date Display As ########

Apr 29, 2008

I have one service level sheet where i used data in time format. I am getting error while using this sheet. I will just explain it in simple way...............

Please find below mentioned details.

Cell C D F

1 23:04 23:13 00:03

In cell G1 i have formula to get time difference between cell C1 and D1 and its give me difference between these both time as 00:09.

Formula is =IF(OR(ISBLANK(C1),ISBLANK(D1)),"",D1-C1)

In cell H1 i have formula to get difference time between cell C1 and F1.

Formula is =IF(OR(ISBLANK(C1),ISBLANK(F1)),"",F1-C1)

But in Cell H1 i am getting Error as Negative time or date display as ########

Is there anyway where i can get Exact time diffrence between Cell F1 and Cell C1

## Formula Returning Negative Time

Aug 9, 2009

the Times work fine 22:00pm to 5:30am or 6:00am to 5:30am

Formulas
J4

=IF(ISNUMBER(D4),(D4>E4)*MEDIAN(0,E4-N\$4,O\$4-N\$4)+MAX(0,MIN(O\$4,E4+(D4>E4))-MAX(N\$4,D4))-F4-L4,0)

K4

=IF(ISNUMBER(D4),MAX(0,MOD(E4-D4,1)-J4-L4-M4-I4-F4),0)

L4

=IF(ISNUMBER(D4),IF(MOD(E4-D4,1)-F4>P\$4,(MOD(D4+P\$4,1)>E4)*MEDIAN(0,E4-N\$4,O\$4-N\$4)+MAX(0,MIN(O\$4,E4+(MOD(D4+P\$4,1)>E4))-MAX(N\$4,MOD(D4+P\$4,1))),0),0)

M4

=IF(ISNUMBER(D4),MAX(MOD(E4-D4,1)-F4-I4-L4-P\$4,0),0)

but if I try 22:00pm to 6:00am or later

The following is displayed

Negative Time in J4 and the wrong result in K4

this is not what I need

It should be showing the following

J4 2:00 K4 5:00 L4 00:00 M4 00:00

## Conditional Formatting - Objects (Red Arrow Points Down To Signify Negative Change)

Apr 8, 2009

I have two arrows:
- Red Arrow points down to signify negative change
- Green Arrow points up to signify positive change

These arrows look exactly like the Excel 2007 conditional formatting arrows you would apply to a cell - the only difference is that I have inserted them as shapes so I can float them over a graph.

GOAL: Corresponding with the graph, if a cell shows a (+) change, then I display green arrow and hide red arrow. Vice versa for a (-) change.

## Formula To Show Time In Hours Going Minus Instead Of Negative

Nov 4, 2012

I have devised a simple formula when a member of staff enters a date and time into a cell it starts deducting from todays date and time. ie:

Cell A1 - Date and time is entered by staff

Cell B1 - Is the above cell +24hrs

Cell C1 - NOW()

Cell D1 - B1-C1 Formatted in hours ( [h] :mm:ss (Like a count down clock)

The problem i have is that i cannot get Cell D1 to show minus ours it just goes to negative and shows continous #####. Is there any way i can get Cell D1 to show the hours it has gone minus by.

## Excel 2010 :: Adding Negative Time Values

Mar 16, 2013

I have a problem with adding time values. The idea of my spreadsheet is to count time worked, and then calculate any variance from the normal shift length (i.e. 7h 30m). My formulas work fine until I work less than 7h 30m in a shift.

Example:

A1=start time
A2=end time
A3=IF(A2="","",MOD(A2-A1,1))
A4=IF(A3="","",(A3-(TIME(7,30,0))))

When the duration is over 7h 30m, A4 correctly returns the value of extra minutes. But when the duration is under 7h 30m the return is #####.

PS A1:A2 are formatted in a custom format hh:mm and A3:A4 are formatted as h:mm, and I'm using Win7 & MS Office 2010.

## Dealing With Cells That Contain #N/A

Sep 12, 2007

Cells A1:D1 contain the following data and have been derived at via a calculation:

A1 = 100
B1 = 150
C1 = 125
D1 = #N/A

etc. etc.

Under certain circumstances, I force the #N/A value to appear rather than have a blank or zero value (the data is used in the production of a line chart).

I would also like to sum A1:D1 (although it could be A1:IV1 - If you know what I mean).

I have tried the following but it does not work.

{=SUM(IF(ISNA(A1:D1),NA(),A1:D1))}

Can anyone explain why ? and what I need to do to correct it ?

Interestingly, the following does work but the calculation returns a Zero value if all four cells contain #N/A (I would like it to return #N/A)

{=SUM(IF(ISNA(A1:D1),0,A1:D1))}

## Excel 2010 :: Rounding Negative (and Positive) Time Values?

Apr 24, 2013

EXCEL 2010. I have a column of negative and positive times (eg. -00:52, -03:07, 06:02) in custom hh:mm format, and would like a new column rounding these times to the nearest negative or positive hour (with just the whole hours displayed), so I need a formula that would give me (for this example) -1, -3, 6, etc.

## Macro Dealing With Images

Aug 11, 2008

I am in serious need of a macro that will search a folder for image names and replace them witha determined image name col. If this is possible it would be a live saver.

For an example

Skus.................Image Name from File (Imported)........Renamed Image

725564..............725564.jpg.................................."Text that I insert"
894646..............atol-894646.jpg............................"Text that I insert"
713246..............713246-atoll.jpg..........................."Text that I insert"

So baically I need images to be searched by sku and replaced with determined image name from a different column.

## Dealing With Formulas In Multiple Sheet

Jan 16, 2012

I am setting up an excel sheet, which requires over 40 sheets + an Input Sheet. The sheets are names, sheet 1, sheet 2, sheet 3...

now, cell A2 in sheet 1 uses a formula, say:
5.42*Input!A2

Cell A2 in sheet 2, would have the formula:
5.42*Input!B2

so and so so forth.

Since I am dealing with over 40 sheets, Is there any way of simplifying this process rather than manually typing out the formula in each of the 40 sheets (especially since each sheet would have over 40 rows, with Sheet 1, linking to Column A in the input sheet, Sheet 2 linking to column B and so on and so forth).

## If Statement Dealing With Color Background

Nov 19, 2009

I am trying to write an If statement that would search a column for cells that have red as a background color, and if they did, would mark the cell with an X.

## Get Excel To Ignore Seconds When Dealing With Times?

Nov 15, 2011

How to get Excel to ignore "seconds" when dealing with times? That is, I NEED the seconds "portion" displayed, but need it to read "00" whenever any times are entered... So, when I type "02:34:44", I really need it to look like "02:34:00" (again, I need the "00" to still display).

Copyrights 2005-15 www.BigResource.com, All rights reserved