IsTime Function?

[Gordon Bentley-Mix]

G'day!

I've been struggling with this for a long time now (no pun intended). VBA
provides this wonderfully convenient IsDate function for checking to see if a
value is a date, but there doesn't seem to be any simple equivalent "IsTime"
function for checking to see if a value is a time. Does anybody have a custom
function for doing this?

Also, the Format function does a terrible job of formatting a times. Any
tips on how to do this?

This has been so problematic in the past that I've had to implement a
workaround by setting up a couple of comboboxes for selecting the time - one
for the hour and another for the minutes (in 5 minute increments) - and
option buttons for AM/PM. Surely there must be a better way...
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[macropod]

hi Gordon,

IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub

 

[Gordon Bentley-Mix]

Hmm... an interesting possibility...

I wonder how "fault-tolerant" it is - how well it does at recognising a
value as a valid time. For example, what does it make of something like
"2pm"? (Evaluates to "True"...)

Even so I'm still not sure if that will help me with the crap job that the
Format function does, which appears to be possessed and never quite
translates what is input into exactly what is desired. For example:

Format("14:00","h:mm AMPM")

returns (as expected)

2:00 p.m.

as does

Format("2pm","h:mm AMPM")

but

Format("1400","h:mm AMPM")"

comes back as

12:00 a.m.

as does

Format("2","h:mm AMPM")"

apparently because, according to IsDate (which must be called implicitly by
Format), "1400" and "2" aren't dates...

So that then leads into the whole User Ed issue - trying to train the users
on how to enter a value that will actually produce the result they're looking
for; e.g. enter "14:00" or "2pm" to get "2:00 p.m."

Anyway, thanks for the tip. I can see some possibilities here, altho I still
think User Ed will be the biggest barrier. I'm not dealing with the brightest
bulbs in the marquee here. Might be better off with the comboboxes/option
buttons approach...

I'll give it a try and let you know how I get on.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

hi Gordon,

IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]

G'day!

I've been struggling with this for a long time now (no pun intended). VBA
provides this wonderfully convenient IsDate function for checking to see if a
value is a date, but there doesn't seem to be any simple equivalent "IsTime"
function for checking to see if a value is a time. Does anybody have a custom
function for doing this?

Also, the Format function does a terrible job of formatting a times. Any
tips on how to do this?

This has been so problematic in the past that I've had to implement a
workaround by setting up a couple of comboboxes for selecting the time - one
for the hour and another for the minutes (in 5 minute increments) - and
option buttons for AM/PM. Surely there must be a better way...
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Greg Maxey]

Gordon,

Perhaps you could manipulate whatever the user enters into a valid time
format then use format. Something like:

Sub Test()
Dim pStr As String
pStr = InputBox("Enter the time: ", "Time Input", "12:00")
Select Case True
Case Is = pStr Like ("#") 'for an inputbox entry of 2
pStr = pStr & "pm"
pStr = Format(pStr, "h:mm AMPM")
Case Is = pStr Like ("####")
pStr = Left(pStr, 2) & ":" & Right(pStr, 2) for an inputbox entry of
1400
pStr = Format(pStr, "h:mm AMPM")
End Select
MsgBox pStr
End Sub

Hmm... an interesting possibility...

I wonder how "fault-tolerant" it is - how well it does at recognising
a value as a valid time. For example, what does it make of something
like "2pm"? (Evaluates to "True"...)

Even so I'm still not sure if that will help me with the crap job
that the Format function does, which appears to be possessed and
never quite translates what is input into exactly what is desired.
For example:

Format("14:00","h:mm AMPM")

returns (as expected)

2:00 p.m.

as does

Format("2pm","h:mm AMPM")

but

Format("1400","h:mm AMPM")"

comes back as

12:00 a.m.

as does

Format("2","h:mm AMPM")"

apparently because, according to IsDate (which must be called
implicitly by Format), "1400" and "2" aren't dates...

So that then leads into the whole User Ed issue - trying to train the
users on how to enter a value that will actually produce the result
they're looking for; e.g. enter "14:00" or "2pm" to get "2:00 p.m."

Anyway, thanks for the tip. I can see some possibilities here, altho
I still think User Ed will be the biggest barrier. I'm not dealing
with the brightest bulbs in the marquee here. Might be better off
with the comboboxes/option buttons approach...

I'll give it a try and let you know how I get on.

hi Gordon,

IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]

G'day!

I've been struggling with this for a long time now (no pun
intended). VBA provides this wonderfully convenient IsDate function
for checking to see if a value is a date, but there doesn't seem to
be any simple equivalent "IsTime" function for checking to see if a
value is a time. Does anybody have a custom function for doing this?

Also, the Format function does a terrible job of formatting a
times. Any tips on how to do this?

This has been so problematic in the past that I've had to implement
a workaround by setting up a couple of comboboxes for selecting the
time - one for the hour and another for the minutes (in 5 minute
increments) - and option buttons for AM/PM. Surely there must be a
better way... --
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please
post all follow-ups to the newsgroup.

 

[macropod]

Hi Gordon,

Since dates are integer values and times are decimal/factional values, it's easy enough to distinguish between the two. For example,
Sub Test()
Dim DtTm As String
DtTm = InputBox("Please input a date and/or time")
If IsDate(DtTm) Then
If Int(CDate(DtTm)) > 0 Then MsgBox "The input date was: " & Format(CDate(DtTm), "dddd, d MMMM yyyy")
If CDate(DtTm) - Int(CDate(DtTm)) > 0 Then MsgBox "The input time was: " & Format(CDate(DtTm) - Int(CDate(DtTm)), "H:mm:ssAm/PM")
Else
MsgBox DtTm & " is not a valid date and/or time entry."
End If
End Sub



--
Cheers
macropod
[MVP - Microsoft Word]

Hmm... an interesting possibility...

I wonder how "fault-tolerant" it is - how well it does at recognising a
value as a valid time. For example, what does it make of something like
"2pm"? (Evaluates to "True"...)

Even so I'm still not sure if that will help me with the crap job that the
Format function does, which appears to be possessed and never quite
translates what is input into exactly what is desired. For example:

Format("14:00","h:mm AMPM")

returns (as expected)

2:00 p.m.

as does

Format("2pm","h:mm AMPM")

but

Format("1400","h:mm AMPM")"

comes back as

12:00 a.m.

as does

Format("2","h:mm AMPM")"

apparently because, according to IsDate (which must be called implicitly by
Format), "1400" and "2" aren't dates...

So that then leads into the whole User Ed issue - trying to train the users
on how to enter a value that will actually produce the result they're looking
for; e.g. enter "14:00" or "2pm" to get "2:00 p.m."

Anyway, thanks for the tip. I can see some possibilities here, altho I still
think User Ed will be the biggest barrier. I'm not dealing with the brightest
bulbs in the marquee here. Might be better off with the comboboxes/option
buttons approach...

I'll give it a try and let you know how I get on.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

hi Gordon,

IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]

G'day!

I've been struggling with this for a long time now (no pun intended). VBA
provides this wonderfully convenient IsDate function for checking to see if a
value is a date, but there doesn't seem to be any simple equivalent "IsTime"
function for checking to see if a value is a time. Does anybody have a custom
function for doing this?

Also, the Format function does a terrible job of formatting a times. Any
tips on how to do this?

This has been so problematic in the past that I've had to implement a
workaround by setting up a couple of comboboxes for selecting the time - one
for the hour and another for the minutes (in 5 minute increments) - and
option buttons for AM/PM. Surely there must be a better way...
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

Thanks guys. You've given me plenty to think about. However, it's a one-off,
very utilitarian type template that I'm working on. This seems like a lot of
work for this application, so I reckon I'll take the low-tech approach for
the time being. But I do have other places where it may be worth the effort,
so I'll file this away for future reference.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

Hi Gordon,

Since dates are integer values and times are decimal/factional values, it's easy enough to distinguish between the two. For example,
Sub Test()
Dim DtTm As String
DtTm = InputBox("Please input a date and/or time")
If IsDate(DtTm) Then
If Int(CDate(DtTm)) > 0 Then MsgBox "The input date was: " & Format(CDate(DtTm), "dddd, d MMMM yyyy")
If CDate(DtTm) - Int(CDate(DtTm)) > 0 Then MsgBox "The input time was: " & Format(CDate(DtTm) - Int(CDate(DtTm)), "H:mm:ssAm/PM")
Else
MsgBox DtTm & " is not a valid date and/or time entry."
End If
End Sub



--
Cheers
macropod
[MVP - Microsoft Word]

Hmm... an interesting possibility...

I wonder how "fault-tolerant" it is - how well it does at recognising a
value as a valid time. For example, what does it make of something like
"2pm"? (Evaluates to "True"...)

Even so I'm still not sure if that will help me with the crap job that the
Format function does, which appears to be possessed and never quite
translates what is input into exactly what is desired. For example:

Format("14:00","h:mm AMPM")

returns (as expected)

2:00 p.m.

as does

Format("2pm","h:mm AMPM")

but

Format("1400","h:mm AMPM")"

comes back as

12:00 a.m.

as does

Format("2","h:mm AMPM")"

apparently because, according to IsDate (which must be called implicitly by
Format), "1400" and "2" aren't dates...

So that then leads into the whole User Ed issue - trying to train the users
on how to enter a value that will actually produce the result they're looking
for; e.g. enter "14:00" or "2pm" to get "2:00 p.m."

Anyway, thanks for the tip. I can see some possibilities here, altho I still
think User Ed will be the biggest barrier. I'm not dealing with the brightest
bulbs in the marquee here. Might be better off with the comboboxes/option
buttons approach...

I'll give it a try and let you know how I get on.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

hi Gordon,

IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


"Gordon Bentley-Mix" <gordon(dot)bentleymix(at)gmail(dot)com> wrote in message
G'day!

I've been struggling with this for a long time now (no pun intended). VBA
provides this wonderfully convenient IsDate function for checking to see if a
value is a date, but there doesn't seem to be any simple equivalent "IsTime"
function for checking to see if a value is a time. Does anybody have a custom
function for doing this?

Also, the Format function does a terrible job of formatting a times. Any
tips on how to do this?

This has been so problematic in the past that I've had to implement a
workaround by setting up a couple of comboboxes for selecting the time - one
for the hour and another for the minutes (in 5 minute increments) - and
option buttons for AM/PM. Surely there must be a better way...
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Peter Jamieson]

If you do format("2","YYYY") and format("1400","YYYY") you'll find out what
dates /format/ thinks 2 and 1400 are, regardless of what IsDate does. When
you try to apply a time format, format reuturns the same values as it always
does (AFAIK) when you supply a date but no time.


--
Peter Jamieson
http://tips.pjmsn.me.uk

Hmm... an interesting possibility...

I wonder how "fault-tolerant" it is - how well it does at recognising a
value as a valid time. For example, what does it make of something like
"2pm"? (Evaluates to "True"...)

Even so I'm still not sure if that will help me with the crap job that the
Format function does, which appears to be possessed and never quite
translates what is input into exactly what is desired. For example:

Format("14:00","h:mm AMPM")

returns (as expected)

2:00 p.m.

as does

Format("2pm","h:mm AMPM")

but

Format("1400","h:mm AMPM")"

comes back as

12:00 a.m.

as does

Format("2","h:mm AMPM")"

apparently because, according to IsDate (which must be called implicitly
by
Format), "1400" and "2" aren't dates...

So that then leads into the whole User Ed issue - trying to train the
users
on how to enter a value that will actually produce the result they're
looking
for; e.g. enter "14:00" or "2pm" to get "2:00 p.m."

Anyway, thanks for the tip. I can see some possibilities here, altho I
still
think User Ed will be the biggest barrier. I'm not dealing with the
brightest
bulbs in the marquee here. Might be better off with the comboboxes/option
buttons approach...

I'll give it a try and let you know how I get on.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post
all
follow-ups to the newsgroup.

hi Gordon,

IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


"Gordon Bentley-Mix" <gordon(dot)bentleymix(at)gmail(dot)com> wrote in
message

G'day!

I've been struggling with this for a long time now (no pun intended).
VBA
provides this wonderfully convenient IsDate function for checking to
see if a
value is a date, but there doesn't seem to be any simple equivalent
"IsTime"
function for checking to see if a value is a time. Does anybody have a
custom
function for doing this?

Also, the Format function does a terrible job of formatting a times.
Any
tips on how to do this?

This has been so problematic in the past that I've had to implement a
workaround by setting up a couple of comboboxes for selecting the
time - one
for the hour and another for the minutes (in 5 minute increments) - and
option buttons for AM/PM. Surely there must be a better way...
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post
all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

Very interesting point Peter. I did a few tests and, based on the results,
think I understand what's going on.

Format("2pm","dddd, d mmmm yyyy, h:mm:ss AMPM")

and

Format("14:00","dddd, d mmmm yyyy, h:mm:ss AMPM")

return

Saturday, 30 December 1899, 2:00:00 p.m.

whereas

Format("2","dddd, d mmmm yyyy, h:mm:ss AMPM")

and

Format("1400","dddd, d mmmm yyyy, h:mm:ss AMPM")

return

Monday, 1 January 1900, 12:00:00 a.m.

and

Saturday, 31 October 1903, 12:00:00 a.m.

respectively.

I'm not quite sure how to explain it succinctly, but I do see what's going
on. It's all based on the "beginning of time" value in Windows: 12:00:00 a.m.
on 30 December 1899. This explains why I was having problems when I was
trying to do some validation on a time to make sure that it's after the
current time; Format is prepending the value with 30 December 1899, which is
_always_ going to be before Now. I can see some gymnastics are going to be
required to make this work. Still not sure if it's worth the effort for my
current application though. Think I'll probably just stick with my low-tech
solution.

Thanks again for the insight. Another data point for the files.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

If you do format("2","YYYY") and format("1400","YYYY") you'll find out what
dates /format/ thinks 2 and 1400 are, regardless of what IsDate does. When
you try to apply a time format, format reuturns the same values as it always
does (AFAIK) when you supply a date but no time.


--
Peter Jamieson
http://tips.pjmsn.me.uk

Hmm... an interesting possibility...

I wonder how "fault-tolerant" it is - how well it does at recognising a
value as a valid time. For example, what does it make of something like
"2pm"? (Evaluates to "True"...)

Even so I'm still not sure if that will help me with the crap job that the
Format function does, which appears to be possessed and never quite
translates what is input into exactly what is desired. For example:

Format("14:00","h:mm AMPM")

returns (as expected)

2:00 p.m.

as does

Format("2pm","h:mm AMPM")

but

Format("1400","h:mm AMPM")"

comes back as

12:00 a.m.

as does

Format("2","h:mm AMPM")"

apparently because, according to IsDate (which must be called implicitly
by
Format), "1400" and "2" aren't dates...

So that then leads into the whole User Ed issue - trying to train the
users
on how to enter a value that will actually produce the result they're
looking
for; e.g. enter "14:00" or "2pm" to get "2:00 p.m."

Anyway, thanks for the tip. I can see some possibilities here, altho I
still
think User Ed will be the biggest barrier. I'm not dealing with the
brightest
bulbs in the marquee here. Might be better off with the comboboxes/option
buttons approach...

I'll give it a try and let you know how I get on.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post
all
follow-ups to the newsgroup.

hi Gordon,

IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


"Gordon Bentley-Mix" <gordon(dot)bentleymix(at)gmail(dot)com> wrote in
message
G'day!

I've been struggling with this for a long time now (no pun intended).
VBA
provides this wonderfully convenient IsDate function for checking to
see if a
value is a date, but there doesn't seem to be any simple equivalent
"IsTime"
function for checking to see if a value is a time. Does anybody have a
custom
function for doing this?

Also, the Format function does a terrible job of formatting a times.
Any
tips on how to do this?

This has been so problematic in the past that I've had to implement a
workaround by setting up a couple of comboboxes for selecting the
time - one
for the hour and another for the minutes (in 5 minute increments) - and
option buttons for AM/PM. Surely there must be a better way...
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post
all
follow-ups to the newsgroup.

 

[Greg Maxey]

Very interesting point Peter. I did a few tests and, based on the results,
think I understand what's going on.

Format("2pm","dddd, d mmmm yyyy, h:mm:ss AMPM")

and

Format("14:00","dddd, d mmmm yyyy, h:mm:ss AMPM")

return

Saturday, 30 December 1899, 2:00:00 p.m.

whereas

Format("2","dddd, d mmmm yyyy, h:mm:ss AMPM")

and

Format("1400","dddd, d mmmm yyyy, h:mm:ss AMPM")

return

Monday, 1 January 1900, 12:00:00 a.m.

and

Saturday, 31 October 1903, 12:00:00 a.m.

respectively.

I'm not quite sure how to explain it succinctly, but I do see what's going
on. It's all based on the "beginning of time" value in Windows: 12:00:00 a.m.
on 30 December 1899. This explains why I was having problems when I was
trying to do some validation on a time to make sure that it's after the
current time; Format is prepending the value with 30 December 1899, whichis
_always_ going to be before Now. I can see some gymnastics are going to be
required to make this work. Still not sure if it's worth the effort for my
current application though. Think I'll probably just stick with my low-tech
solution.

Thanks again for the insight. Another data point for the files.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

If you do format("2","YYYY") and format("1400","YYYY") you'll find out what
dates /format/ thinks 2 and 1400 are, regardless of what IsDate does. When
you try to apply a time format, format reuturns the same values as it always
does (AFAIK) when you supply a date but no time.

Hmm... an interesting possibility...
I wonder how "fault-tolerant" it is - how well it does at recognisinga
value as a valid time. For example, what does it make of something like
"2pm"? (Evaluates to "True"...)
Even so I'm still not sure if that will help me with the crap job that the
Format function does, which appears to be possessed and never quite
translates what is input into exactly what is desired. For example:
Format("14:00","h:mm AMPM")
returns (as expected)
2:00 p.m.
as does
Format("2pm","h:mm AMPM")
but
Format("1400","h:mm AMPM")"
comes back as
12:00 a.m.
as does
Format("2","h:mm AMPM")"
apparently because, according to IsDate (which must be called implicitly
by
Format), "1400" and "2" aren't dates...
So that then leads into the whole User Ed issue - trying to train the
users
on how to enter a value that will actually produce the result they're
looking
for; e.g. enter "14:00" or "2pm" to get "2:00 p.m."
Anyway, thanks for the tip. I can see some possibilities here, altho I
still
think User Ed will be the biggest barrier. I'm not dealing with the
brightest
bulbs in the marquee here. Might be better off with the comboboxes/option
buttons approach...
I'll give it a try and let you know how I get on.
--
Cheers!
Gordon
Uninvited email contact will be marked as SPAM and ignored. Please post
all
follow-ups to the newsgroup.
:
hi Gordon,
IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub
--
Cheers
macropod
[MVP - Microsoft Word]
"Gordon Bentley-Mix" <gordon(dot)bentleymix(at)gmail(dot)com> wrote in
message
G'day!
I've been struggling with this for a long time now (no pun intended).
VBA
provides this wonderfully convenient IsDate function for checking to
see if a
value is a date, but there doesn't seem to be any simple equivalent
"IsTime"
function for checking to see if a value is a time. Does anybody have a
custom
function for doing this?
Also, the Format function does a terrible job of formatting a times.
Any
tips on how to do this?
This has been so problematic in the past that I've had to implement a
workaround by setting up a couple of comboboxes for selecting the
time - one
for the hour and another for the minutes (in 5 minute increments) - and
option buttons for AM/PM. Surely there must be a better way...
--
Cheers!
Gordon
Uninvited email contact will be marked as SPAM and ignored. Pleasepost
all
follow-ups to the newsgroup.- Hide quoted text -

- Show quoted text -

Gordon,

I sent you a document that contains a routine to turn text entries
such as:

1400
2
9:23

etc. into formatted time entries.

Since the message was uninvited it may have landed in your spam
folder.

 

[Gordon Bentley-Mix]

Nah. I got it Greg. (You're on the 'whitelist' so everything you send gets
through - cuz I know you wouldn't send me rubbish! <g>) I just haven't had a
chance to look at it yet - too busy with "production issues". Also it's on my
personal machine and not the client machine that I'm developing on. I'll send
it to myself and have a squizz now.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

Greg,

I had a look at your code and I'm pretty sure it would do the trick if
incorporated into an Exit macro, altho I think the approach I'm using works
equally well. (I'll send you a copy of the template later today.) However,
ultimately I'd like to do away with the option buttons and just have
something that would take a value like "2" and automatically make it "2:00
p.m.". I think this should be possible if some further constraints are placed
on the acceptable values - specifically that they must resolve to a time
between 8:00 a.m. and 5:30 p.m. In this scenario there would be only one
possible "interpretation" of any given value, and values between 5:31 and
7:59 would be right out.

Sorry for not providing this info on the limits for acceptable values
previously, but it only became apparent when I started looking at validating
the time value input using the functionality in my template and realised that
these limits (based on the client's normal business hours) actually existed.

What I have now works quite well, altho it does rely on "ambulance at the
bottom" validation rather than "fence at the top". I may look at ways of
limiting the acceptable values in accordance with the above today - perhaps
by changing the way that the comboboxes are populated and possibly getting
rid of the AM/PM option buttons.

I may also want to do something around making sure that the date and time
that's entered is at least some set amount in the future - which could be a
bit more difficult in that it will have to take into account the amount of
time left in the current business day, as well as "hours of unavailabilty"
created by weekends and such - basically codifying the SLA business rules.
Might be a bit ambitious tho and may be something best left to
"administrative control".

Anyway, thanks for help and the nudge in the right direction. Expect a bit
of reciprocity soon.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Greg Maxey]

Ok. Thanks.

Greg,

I had a look at your code and I'm pretty sure it would do the trick if
incorporated into an Exit macro, altho I think the approach I'm using
works equally well. (I'll send you a copy of the template later
today.) However, ultimately I'd like to do away with the option
buttons and just have something that would take a value like "2" and
automatically make it "2:00 p.m.". I think this should be possible if
some further constraints are placed on the acceptable values -
specifically that they must resolve to a time between 8:00 a.m. and
5:30 p.m. In this scenario there would be only one possible
"interpretation" of any given value, and values between 5:31 and 7:59
would be right out.

Sorry for not providing this info on the limits for acceptable values
previously, but it only became apparent when I started looking at
validating the time value input using the functionality in my
template and realised that these limits (based on the client's normal
business hours) actually existed.

What I have now works quite well, altho it does rely on "ambulance at
the bottom" validation rather than "fence at the top". I may look at
ways of limiting the acceptable values in accordance with the above
today - perhaps by changing the way that the comboboxes are populated
and possibly getting rid of the AM/PM option buttons.

I may also want to do something around making sure that the date and
time that's entered is at least some set amount in the future - which
could be a bit more difficult in that it will have to take into
account the amount of time left in the current business day, as well
as "hours of unavailabilty" created by weekends and such - basically
codifying the SLA business rules. Might be a bit ambitious tho and
may be something best left to "administrative control".

Anyway, thanks for help and the nudge in the right direction. Expect
a bit of reciprocity soon.

Very interesting point Peter. I did a few tests and, based on the
results, think I understand what's going on.

Format("2pm","dddd, d mmmm yyyy, h:mm:ss AMPM")

and

Format("14:00","dddd, d mmmm yyyy, h:mm:ss AMPM")

return

Saturday, 30 December 1899, 2:00:00 p.m.

whereas

Format("2","dddd, d mmmm yyyy, h:mm:ss AMPM")

and

Format("1400","dddd, d mmmm yyyy, h:mm:ss AMPM")

return

Monday, 1 January 1900, 12:00:00 a.m.

and

Saturday, 31 October 1903, 12:00:00 a.m.

respectively.

I'm not quite sure how to explain it succinctly, but I do see
what's going on. It's all based on the "beginning of time" value in
Windows: 12:00:00 a.m. on 30 December 1899. This explains why I was
having problems when I was trying to do some validation on a time
to make sure that it's after the current time; Format is prepending
the value with 30 December 1899, which is _always_ going to be
before Now. I can see some gymnastics are going to be required to
make this work. Still not sure if it's worth the effort for my
current application though. Think I'll probably just stick with my
low-tech solution.

Thanks again for the insight. Another data point for the files.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please
post all follow-ups to the newsgroup.



:
If you do format("2","YYYY") and format("1400","YYYY") you'll find
out what dates /format/ thinks 2 and 1400 are, regardless of what
IsDate does. When you try to apply a time format, format reuturns
the same values as it always does (AFAIK) when you supply a date
but no time.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Gordon Bentley-Mix" <gordon(dot)bentleymix(at)gmail(dot)com>
wrote in
messageHmm... an interesting possibility...

I wonder how "fault-tolerant" it is - how well it does at
recognising a value as a valid time. For example, what does it
make of something like "2pm"? (Evaluates to "True"...)

Even so I'm still not sure if that will help me with the crap job
that the Format function does, which appears to be possessed and
never quite translates what is input into exactly what is
desired. For example:

Format("14:00","h:mm AMPM")

returns (as expected)

2:00 p.m.

as does

Format("2pm","h:mm AMPM")

but

Format("1400","h:mm AMPM")"

comes back as

12:00 a.m.

as does

Format("2","h:mm AMPM")"

apparently because, according to IsDate (which must be called
implicitly by
Format), "1400" and "2" aren't dates...

So that then leads into the whole User Ed issue - trying to train
the users
on how to enter a value that will actually produce the result
they're looking
for; e.g. enter "14:00" or "2pm" to get "2:00 p.m."

Anyway, thanks for the tip. I can see some possibilities here,
altho I still
think User Ed will be the biggest barrier. I'm not dealing with
the brightest
bulbs in the marquee here. Might be better off with the
comboboxes/option buttons approach...

I'll give it a try and let you know how I get on.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored.
Please post all
follow-ups to the newsgroup.

:

hi Gordon,

IsDate works for both dates and times. For example:
Sub Test()
MsgBox IsDate("21:59")
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]

"Gordon Bentley-Mix" <gordon(dot)bentleymix(at)gmail(dot)com>
wrote in message
G'day!

I've been struggling with this for a long time now (no pun
intended). VBA
provides this wonderfully convenient IsDate function for
checking to see if a
value is a date, but there doesn't seem to be any simple
equivalent "IsTime"
function for checking to see if a value is a time. Does anybody
have a custom
function for doing this?

Also, the Format function does a terrible job of formatting a
times. Any
tips on how to do this?

This has been so problematic in the past that I've had to
implement a workaround by setting up a couple of comboboxes for
selecting the
time - one
for the hour and another for the minutes (in 5 minute
increments) - and option buttons for AM/PM. Surely there must
be a better way... --
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored.
Please post all
follow-ups to the newsgroup.- Hide quoted text -

- Show quoted text -

Gordon,

I sent you a document that contains a routine to turn text entries
such as:

1400
2
9:23

etc. into formatted time entries.

Since the message was uninvited it may have landed in your spam
folder.