How can I verify that a cell contains a valid time?

[USAMax]

I currently have a cell that reads 8:00 AM or .3333 depending on how it is formatted. The cell will always be formatted "h:mm AM/PM" but could be blank, have a space " " or have a time including 12:00 AM which is 0. Sometimes IsDate works but not always.

How can I verify that a cell contains a valid time?

 

[Rick Rothstein]

I currently have a cell that reads 8:00 AM or .3333 depending on how it is formatted. The cell will always be formatted "h:mm AM/PM" but could be blank, have a space " " or have a time including 12:00 AM which is 0. Sometimes IsDate works but not always.

How can I verify that a cell contains a valid time?

I think first you have to tell us why you think 12:00 AM is not a valid time (otherwise we will not know how to construct the VB code).

 

[bosco_yip]

1] A1 =8:00A.M.

2] A2 =0.3333

3] Cell B1 enter the formula and copy down to B2 :

=IF(LEFT(CELL("format",A1))="D","Valid Time","Value")

Regards

 

[USAMax]

I was not very clear when explaining my problem. 12:00 AM is a valid time but as its value is Zero my testing has not worked for this time.

My biggest error was not explaining that I need to know how to detect this in VBA and not on the worksheet. I will be working with different durations such as 1:15 or one hour and 15 minutes and adding this to the time of day. For example: 8:00 AM plus 1:15 is 9:15 AM. I need to validate the times in both formats.

My thanks to both of you for your input.

 

[Rick Rothstein]

I was not very clear when explaining my problem. 12:00 AM is a valid time but as its value is Zero my testing has not worked for this time.

My biggest error was not explaining that I need to know how to detect this in VBA and not on the worksheet. I will be working with different durations such as 1:15 or one hour and 15 minutes and adding this to the time of day. For example: 8:00 AM plus 1:15 is 9:15 AM. I need to validate the times in both formats.

Assuming the cell you want to check will never contain a number for a time value over 24 hours (since that would show as a time but be for the next day), then I think this should work...

Function IsRealTime(Cell As Range) As Boolean
  IsRealTime = (Len(Trim(Cell.Value)) > 0 And Cell.Value >= 0 And Cell.Value < 1)
End Function

 

[USAMax]

This looks sound but I am not sure it will work when the cell has a " " but I am happy to try it.

I will be testing data for the next two hours and if it works I will let you know tomorrow.

Thank you Rick!

 

[Rick Rothstein]

This looks sound but I am not sure it will work when the cell has a " " but I am happy to try it.

I will be testing data for the next two hours and if it works I will let you know tomorrow.

It will work for a space (or any arbitrary text for that matter) entered into the cell. Yes, definitely let us know how it worked for you and, if it doesn't work, please describe under what situation so we can patch the code for you.

 

[USAMax]

Assuming the cell you want to check will never contain a number for a time value over 24 hours (since that would show as a time but be for the next day), then I think this should work...

Function IsRealTime(Cell As Range) As Boolean
  IsRealTime = (Len(Trim(Cell.Value)) > 0 And Cell.Value >= 0 And Cell.Value < 1)
End Function

Good morning Rick,

Thank you again for the perfect solution. Your code was so elegant that it has inspired me to change several of my existing functions to work the same way.

In reality, you gave me three solutions while helping me with one.

Thank you!