CODEWITHSUNDEEP
exceltimeformattingopenoffice-calclibreoffice-calc
Amazon Dies In Darkness
Amazon Dies In Darkness

Reputation: 5833

LibreOffice Calc / OpenOffice Calc / Excel: How to display a negative time duration?

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".

Upvotes: 2

Views: 1173

Answers (2)

Hermann Schwarz
Hermann Schwarz

Reputation: 1735

in LibreOffice Calc you should format the cells as

[HH]:MM

to use negative times and hours.

Upvotes: 5

Tom Sharpe
Tom Sharpe

Reputation: 34265

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

Upvotes: 2

Related Questions