Time Issue
Hi all the experts,
I need a little help. I am entering time into a rather large spreadsheet. I need to bypass the colon entering part as it slows the process down immensely.
Is there anyway to just enter the 4 digits and create a cell format to change it to time. But still be able to use it in calculations.
Thank you in advance
Time.jpg
http://www.excelforum.com/attachment...1&d=1457594677
Perhaps try http://www.cpearson.com/Excel/DateTimeEntry.htm or Google around for " Military time"
try this
Format Time ON and Time OUT cells
this inserts ":" colon into your number (it will always remain a number that looks like time)
0\:00
ie: when you input
800 it displays as 8:00
830 it displays as 8:30
etc
then copy down your required range
in E6 put the following formula and copy down
format as [h]:mm=IF(COUNT(B6,C6)=2,MAX(0,MIN(TEXT(C6,"00\:00")+(TEXT(B6,"00\:00")>TEXT(C6,"00\:00")))-MAX(TEXT(B6,"00\:00"))),"")
I have noticed further down that you have the following formula
D259
it still gives the required result=IF(G259<6/24,"SS",IF(G259>8.33/24,TIME(8,20,0),G259))
hope this helps you
Toonies
if you still want a vba solution
this is created by on of the Excel Guru's on here(I cannot recall who) and I take no credit it's all theirs
800 it displays as 8:00
830 it displays as 8:30
etc
I have put the 1st 5 ranges in
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Add additional ranges If Application.Intersect(Target, Range("B6:C36, B48:C78, B90:C120, B132:C162, B174:C204")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target On Error GoTo Oops Application.EnableEvents = False .Value = Dec2HM(.Value) Oops: Application.EnableEvents = True End With End Sub Function Dec2HM(iVal As Long) As Date ' converts 1234 to 12:34 Dec2HM = CDate((iVal \ 100) / 24 + _ (iVal Mod 100) / 1440) End Function
Last edited by Toonies; 03-10-2016 at 06:37 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks