• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Solved Is there another way for Excel to show negative times


wiganken

Member
Member
Posts
105
#1
Because Excel cannot show negative times (except as an infinite number of ## characters) I have devised a way for Excel to show that times (hh:mm) are negative by inserting a column that shows "Exceeded" before the "Exceeded By" value. The attached picture shows what I have done. Is there a better or easier way to show that cell B2 exceeds A2?
 

Attachments

My Computer

System One

  • OS
    Windows 8.1 64 bit
    Computer type
    PC/Desktop

mrjimphelps

"Phelps Helps"
Posts
121
#2
You can convert the time to an integer, and then subtract one integer from the other. Since integers are either positive or negative, if you display the result as an integer; it will be either positive or negative, which will accomplish your purpose. Or, convert the result back to time format; and if it was negative, include the word "Exceeded" in what is displayed.
 

My Computer

System One

  • OS
    Linux Mint 18.2 xfce 64-bit (VMWare host) / Windows 8.1 Pro 32-bit (VMWare guest)
    Computer type
    PC/Desktop
    System Manufacturer/Model Number
    Dell
    CPU
    Haswell
    Memory
    4 GB
    Monitor(s) Displays
    Acer 23"
    Screen Resolution
    1920 x 1080
    Hard Drives
    Two hard drives, 1TB each: One for Linux, one for my data.
    Keyboard
    IBM Model M
    Browser
    Firefox, Opera
    Antivirus
    Sophos (Linux), Trend Micro (Windows)
    Other Info
    I use Samba to share my data drive with the other computers at my house and with my guest session in VMWare Workstation Player.

wiganken

Member
Member
Posts
105
#3
By integers I think you mean converting the hh:mm to a decimal number so 15:45 (hh:mm) becomes 15.75? I don't mean to be pedantic but 15.75 is not an integer because, by definition, integers have to be whole numbers (+ve or -ve). I do get your point but I think it is likely to be more complicated than my method. Thanks for your input. All help is appreciated.
 

My Computer

System One

  • OS
    Windows 8.1 64 bit
    Computer type
    PC/Desktop

mrjimphelps

"Phelps Helps"
Posts
121
#4
You can specify TIMEVALUE(15:45) to get an integer equivalent for the time. You can compare TIMEVALUE(A2) to TIMEVALUE (B2) to see which is greater, and make your decision based on that.
 

My Computer

System One

  • OS
    Linux Mint 18.2 xfce 64-bit (VMWare host) / Windows 8.1 Pro 32-bit (VMWare guest)
    Computer type
    PC/Desktop
    System Manufacturer/Model Number
    Dell
    CPU
    Haswell
    Memory
    4 GB
    Monitor(s) Displays
    Acer 23"
    Screen Resolution
    1920 x 1080
    Hard Drives
    Two hard drives, 1TB each: One for Linux, one for my data.
    Keyboard
    IBM Model M
    Browser
    Firefox, Opera
    Antivirus
    Sophos (Linux), Trend Micro (Windows)
    Other Info
    I use Samba to share my data drive with the other computers at my house and with my guest session in VMWare Workstation Player.

wiganken

Member
Member
Posts
105
#5
It converts times into decimal parts of the day and gives the answer in same format so not what I was after. I wanted to stick with the standard hh:mm time format for simplicity. I think I will stick with my original idea but thanks for the input (you are the only one who tried to help :) ). I'll call this thread closed now and stop following it.
 

My Computer

System One

  • OS
    Windows 8.1 64 bit
    Computer type
    PC/Desktop