I use a LibreOffice/OpenOffice spreadsheet to track my sleep.
Column A contains the time I fell asleep, and column B contains the time I woke up.
Column C contains an "adjustment"; if I woke up early, and then went back to sleep, I'll subtract the duration I was awake. Similarly, if I take a nap, I'll add in the duration I napped.
Column D computes the duration of sleep: =(B1-A1)+C1
The trouble I'm having is visually representing column C effectively. I want it to show "-1:15" if I woke up early, was awake for 1 hour and 15 minutes, and then went back to bed. Conversely, I want it to show "+1:45" if I took a nap for an hour and 45 minutes.
How can I format column C to do this? I've tried several custom formatting options, and none of my attempts have succeeded thus far. Everything I have tried winds up displaying "22:45" instead of "-1:15".
There is a hack that you can use in Excel
Go into Options|Advanced|When calculating this workbook and tick 'Use 1904 date system'.
Then it will let you set the format to +hh:mm;-hh:mm and everything should work OK.
In Open Office it will let you use +hh:mm;-hh:mm