Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

TIme format

I have a spreadsheet that has cel formatted [hh]:mm if the user enters a "." instead of a ":" the cell formatting is not correct how tyot i replace the period Example 6.00 should be 06:00.see the attached file.

Thanks
Book1times.xlsm
0
Svgmassive
Asked:
Svgmassive
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Would this be suitable for you?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

  Select Case (True)
  
      Case (Intersect([I17:N17], Target) Is Nothing)
      
      Case (Not (IsNumeric(Target)))
      
      Case (Val(Target) > 1)
          Application.EnableEvents = False
          
          If InStr(CStr(Target), ".") > 0 Then
             Target = Format(Replace(CStr(Target), ".", ":"), "HH:MM")
          Else
             Target = Format(Replace(CStr(Target) & ".00", ".", ":"), "HH:MM")
          End If
          
          Application.EnableEvents = True
      
      Case Else
      
  End Select
  
End Sub

Open in new window


Please (also) see the attached workbook.
Q-28518882.xlsm
0
 
Glenn RayExcel VBA DeveloperCommented:
You could set up Data Validation on these cells to limit entry between 0 and 1 (days, implicit).  That would prevent anyone from entering 6 days as a value.  

You could further enforce this by calculating the fraction of a day that the maximum number of allowable hours is.  If 8 hours is the maximum, the data validation limit would be 0.33333.  If 12 hours, then 0.5.

See the attached example workbook with data validation added.

Regards,
-Glenn
EE-Book1times.xlsx
0
 
Rob HensonIT & Database AssistantCommented:
Alternatively, you can set Data Validation to only accept a Time value. With this you can set a Earliest and Latest time.

However, if you are entering a length of time (rather than a clock time) and the time entered is greater than 24 Hours, the DV won't accept it.

Thanks
Rob H
0
 
SvgmassiveAuthor Commented:
Wonderful exactly what i needed.Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now