Excel Formula

L

LNEVES

JUST TRYING TO GET A CELL TO CONVERT "0830" (WHEN TYPED IN TO IT ) T
SHOW 08:30 IN THE CELL. OR "1400" TO SHOW 02:00 pm OR 14:00 - JUS
TRYING TO MAKE THE DATA ENTRY AS EASY AS POSSIBLE
 
C

CLR

Try formating the cell, Right-click on the cell, > Format cells > Number
tab > Custom, and Type: ##":"##
0830 becomes 8:30, 1400 becomes 14:00

Vaya con Dios,
Chuck, CABGx3
 
P

Paul B

Have a look here

http://www.cpearson.com/excel/DateTimeEntry.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
 
D

Don Guillett

A bit LOUDER since I am hard of hearing. Please do NOT shout (all caps -
considered rude netiquette)
right click sheet tab>view code>insert ALL of this. Change range to suit.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 'Ron Rosenfeld
<[email protected]>
Dim InputRange As Range
Dim Temp As Variant
Dim i As Integer
Dim Separators(2) As String

Separators(0) = ";"
Separators(1) = ","
Separators(2) = "."


Application.EnableEvents = False

Set InputRange = [A1:A100] 'or whatever
If Intersect(Target, InputRange) Is Nothing Then GoTo Bye
If Target.Value = 0 Then GoTo Bye
Temp = Target.Value

'If entry is an integer, then make it a time

On Error GoTo ConvertString
If Int(Temp) = Temp Then
Temp = (Int(Temp / 100) + (Temp / 100 - Int(Temp / 100)) * 100 / 60) /
24
GoTo Last
ElseIf Temp < 1 Then 'probably a time
GoTo Last
End If

If Temp > 15000 Then 'probably a date/time string
Temp = CDate(Temp - Int(Temp))
GoTo Last
End If

ConvertString: 'replace separator with colon unless the string is a
date
On Error GoTo 0
For i = 0 To 2
Temp = Replace(Temp, Separators(i), ":")
Next i


Last:
Target.Value = Temp
Target.NumberFormat = "hh:mm"
Bye: Application.EnableEvents = True
End Sub
 
L

LNEVES

Thank you so much CLR. That was easy, I do apologize for the all caps
as I am new to this - Thanks again for taking the time to help me.
Laur
 
S

Sandy Mann

Chuck,

That works but the data remains as numbers and are not converted to times.
I took it from thethat the OP was entering a series of time entries that he/she will want to
add up.

Regards

Sandy"

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

CLR said:
Try formating the cell, Right-click on the cell, > Format cells > Number
tab > Custom, and Type: ##":"##
0830 becomes 8:30, 1400 becomes 14:00

Vaya con Dios,
Chuck, CABGx3
 
G

Gord Dibben

Not quite that easy!!

See Sandy's posting in response to Chuck's suggestion.

Gord Dibben Excel MVP
 
L

LNEVES

No Sir - Mr. Guillett - I was in fact thanking CLR - not you - to b
honest, yours was confusing, as I stated before I am new to this an
need KISS instructions. The addition of the hours will come after
get more familiar with this system - Sandy you are so correct - and
am working towards that. Thanks again to all for your time.
L
 
D

Don Guillett

I'm glad you are now happy. Sorry Ron's code that I supplied confused you.
You should have tried it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top