Tag Archives: Microsoft Office

Negative Time Values In Excel

I was trying to set up a simple time sheet in Excel. I know that ready-made ones exist, but it’s more fun and educating to make your own, right? And I came across a nasty problem: Excel doesn’t like to display negative time:

No Negative Time In Our Universe, Punk

No Negative Time In Our Universe, Punk

Turns out there’s a simple solution for this: Enable the 1904 dating system. There is also a workaround formatting the result as text, but Microsoft states that this then can not be used for further calculations – not a viable solution for a time sheet!

To enable the 1904 dating system, go to your Excel options. It can be found under “Advanced” – you need to scroll quite a bit down:

The 1904 Dating System

The 1904 Dating System

Enable the option, and lo and behold – it works:

Negative Time!

Negative Time!

See also: Summing up times in Excel.

Excel Formula to get Number of Days in a Month

It seems Excel still can’t tell me the days in a particular month.

The following formula works, though:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

Obviously, A1 has to have a date in it.

Works like a charm:

Example: Days in a Month in Excel

 

Update, much, much later: Looks like LibreOffice has a DAYSINMONTH function.