| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying negative time in excel
I have a worksheet that displays appt. time & actual time in, some of my
actual time in data is greater than the appt. time ex: 13:15 appt time, Actual time in 13:18, the cell should show :03 in red? but instead it gives me a #VALUE!. How can I display this time, and show it as a negative time. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying negative time in excel
1904 date system.
-- David Biddulph "Six Sigma Blackbelt" wrote in message ... I have a worksheet that displays appt. time & actual time in, some of my actual time in data is greater than the appt. time ex: 13:15 appt time, Actual time in 13:18, the cell should show :03 in red? but instead it gives me a #VALUE!. How can I display this time, and show it as a negative time. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying negative time in excel
6,
You need to use the 1904 date system to allow negative times Tools / Options / Calc tab Check "1904 Date system" Note that this will throuw off any entered dates on you worksheet unless you subtract 1492 from all of your date constants.... HTH, Bernie MS Excel MVP "Six Sigma Blackbelt" wrote in message ... I have a worksheet that displays appt. time & actual time in, some of my actual time in data is greater than the appt. time ex: 13:15 appt time, Actual time in 13:18, the cell should show :03 in red? but instead it gives me a #VALUE!. How can I display this time, and show it as a negative time. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying negative time in excel
Use conditional formatting.
A2 = 13:15 B2 = 13:18 C2 = formula: =ABS(A2-B2) C2 conditional formatting: Formula Is: =(A2-B2)*24<0 Set the font color to red OK out Biff "Six Sigma Blackbelt" wrote in message ... I have a worksheet that displays appt. time & actual time in, some of my actual time in data is greater than the appt. time ex: 13:15 appt time, Actual time in 13:18, the cell should show :03 in red? but instead it gives me a #VALUE!. How can I display this time, and show it as a negative time. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying negative time in excel
Try this:
=MAX(A2:B2)-MIN(A2:B2) "Six Sigma Blackbelt" wrote: I have a worksheet that displays appt. time & actual time in, some of my actual time in data is greater than the appt. time ex: 13:15 appt time, Actual time in 13:18, the cell should show :03 in red? but instead it gives me a #VALUE!. How can I display this time, and show it as a negative time. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Displaying negative time in excel
Bernie
Make that 1462 Gord On Fri, 10 Nov 2006 12:38:52 -0500, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 6, You need to use the 1904 date system to allow negative times Tools / Options / Calc tab Check "1904 Date system" Note that this will throuw off any entered dates on you worksheet unless you subtract 1492 from all of your date constants.... HTH, Bernie MS Excel MVP "Six Sigma Blackbelt" wrote in message ... I have a worksheet that displays appt. time & actual time in, some of my actual time in data is greater than the appt. time ex: 13:15 appt time, Actual time in 13:18, the cell should show :03 in red? but instead it gives me a #VALUE!. How can I display this time, and show it as a negative time. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
| Excel file with hyperlinks takes a long time to open over the network | Links and Linking in Excel | |||
| How do I get Excel to display a negative value for time? | Charts and Charting in Excel | |||
| Every second time i open Excel, it displays the document recovery pane | Excel Discussion (Misc queries) | |||
| time interval calculations in excel | Excel Discussion (Misc queries) | |||
Linear Mode
