Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i display a negative time as a res of distracting 2 times?
In a sheet about working time i want to extract two times.
1 is the normal working hours for the day 2 is the actual worked hours on a day If 2 < 1, the result is negative - I worked not enough hours on that day. The result is displayed as ######. Question: Is it posible to display negative hours c.q. times in a sheet? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i display a negative time as a res of distracting 2 times?
If you can settle for a text result,
=IF(A2-A1<0,"-","")&TEXT(ABS(A2-A1),"hh:mm") HTH Kostis Vezerides On Jul 12, 8:56 pm, Red Bullit <Red wrote: In a sheet about working time i want to extract two times. 1 is the normal working hours for the day 2 is the actual worked hours on a day If 2 < 1, the result is negative - I worked not enough hours on that day. The result is displayed as ######. Question: Is it posible to display negative hours c.q. times in a sheet? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i display a negative time as a res of distracting 2 times?
Red,
Visit John Walkenbach's site: http://www.j-walk.com/ss/excel/usertips/tip051.htm *************************** Dealing with negative time values Because Excel stores dates and times as numeric values, it's possible to add or subtract one from the other. However, if you have a workbook containing only times (no dates), you may have discovered that subtracting one time from another doesn't always work. Negative time values appear as a series of hash marks (########), even though you've assigned the [h]:mm format to the cells. By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date/time combination that falls before this date, which is invalid. The solution is to use the optional 1904 date system. Select Tools, Options, click the Calculation tab, and check the 1904 date system box to change the starting date to January 2, 1904. Your negative times will now be displayed correctly, as shown below. Be careful if you workbook contains links to other files that don't use the 1904 date system. In such a case, the mismatch of date systems could cause erroneous results. *********************************** HTH, Bernie MS Excel MVP "Red Bullit" <Red wrote in message ... In a sheet about working time i want to extract two times. 1 is the normal working hours for the day 2 is the actual worked hours on a day If 2 < 1, the result is negative - I worked not enough hours on that day. The result is displayed as ######. Question: Is it posible to display negative hours c.q. times in a sheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i display negative time differently? | Excel Discussion (Misc queries) | |||
How do I get Excel to display a negative value for time? | Charts and Charting in Excel | |||
How do I display negative time values in excell? | Excel Worksheet Functions | |||
How do I display time in a negative format? | Excel Worksheet Functions | |||
What is the formula to display a negative time results? | Excel Worksheet Functions |