CODEWITHSUNDEEP
exceltimevba
micuzzo
micuzzo

Reputation: 165

Midnight as 24:00 not 00:00 in Excel

This isn't explicitly a programming question but maybe I can get around it with VBA.

I have some times I'm working with that go over midnight and Excel is not displaying them because they would be 'negative values'. Eg: 00:30 - 23:30 (or 12:30 am - 11:30pm). This is causing many errors.

Is there a way to get around this? I've tried changing the format to [hh]:mm:ss and this doesn't help. I've also changed the time format to 1904 but this causes problems with other errors I'm using.

Upvotes: 0

Views: 1834

Answers (1)

MortenSickel
MortenSickel

Reputation: 2200

If you are doing calculations With times going cross midnight, you need to add a date, thereby Excel will know which timestamp that is the latest.

i.e.

00:30 - 23:30 does not work

jan 02 2000 00:30 - jan 01 2000 23:30 Works fine

use the actual date or whatever date is suitable, the important thing is just that 00:30 is the day after 23:30

or if you have the from-time in b2 and the to-time in c2:

if(c2>b2;c2;c2+1)-b2

change if and ; to whatever Excel wants in your locale

Upvotes: 1

Related Questions