TIME(13,0,0) v. --"13:00"?

[JoeU2004]

Generally, I believe it is better to use DATE(2009,1,2) instead
of --"1/2/2009" because the interpretation of the latter depends Regional
and Language settings.

What about TIME(13,0,0) v. --"13:00"?

In my (US) Regional and Language settings, ":" is the only separator in the
drop-down list.

But I presume it is a drop-down list for a reason. Do some languages use a
differ time separator, at least optionally?

Do some languages specify time in a different order than hh:mm?

 

[Chip Pearson]

I don't know about other locale's time separator (I've never seen
anything but ":", but that doesn't mean anything conclusive.), but
TIME will roll over at 24 hours. E.g.,

=TIME(36,0,0)

will return 12:00:00 or in serial format 0.5. The (36-24) hours are
lost. But --"36:00:00" will correctly return 36:00:00 or in serial
format 1.5. The difference can be very important.

I generally don't use the TIME function in worksheet functions.
Instead, I use a VBA function:

Function TimeX(H As Long, M As Long, S As Long) As Date
TimeX = TimeSerial(H, M, S)
End Function


This will properly handle hours > 24. For example,

=TimeX(36,0,0)

`will correctly return 36:00:00 or 1.5 in serial format. If H is
negative, the numeric result is correct (=TimeX(-6,0,0) -> -0.25) but
unless you have the 1904 date setting enabled, Excel won't display the
negative time. Instead, it will display #####.

If you don't want to use VBA and have the Hour component of a time in
a cell, say G1, you can use

=TIME(G1,0,0)+INT(G1/24)

to return the correct time, not rounded at 24 hours. It does not,
however, handle negative hours.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)