I wanta be lazy with 01:30 &17:30 input time (Att: Steve) Wo

cblincoln43

Board Regular
Joined
Mar 12, 2002
Messages
206
I saw and can't find how to enter time as 1730 & 0100, and have it end up in the cell as 5:30pm or 1:00am. But not change the calculation for start and end time to hours.

I search i learn that i should have bookmarked 6,000 + items i love every time i go there. Thanks in advance for the help. Back to Search to Learn.
This message was edited by cblincoln43 on 2002-09-01 18:18
This message was edited by cblincoln43 on 2002-09-04 12:16
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

cblincoln43

Board Regular
Joined
Mar 12, 2002
Messages
206
Have given it more thought! I don't think it was a formula that did this conversion. When you ENTER the time into the cell and delete it you lose both time & formula. may have been vba or format (?) I just know the little lady that posted it was given a slick way to do it. She ENTERED time wrong and it corected it to the proper time format.
Still searching.
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

Well, there are possibly two ways to enter time without a delimiter, and have it automatically formatted.

Not that I know them off the top of my head.

One would involve a custom formatting.

The other would involve WorkSheet_Change, and that's where this formula MIGHT be useful.

Workbook_Change has a Target as Range argument; your code would perform an Intersect of Target against the cells where you desire this behavior.

If you are in the correct cell(s), then Target= the formula.
This message was edited by stevebausch on 2002-09-02 08:15
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
OOPs! The Typo Monster has struck! Formula works as advertised. Sorry about that.

Since I am also interested in this, watch this thread, see what develops.
This message was edited by stevebausch on 2002-09-02 08:00
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

On 2002-09-02 07:32, cblincoln43 wrote:
Have given it more thought! I don't think it was a formula that did this conversion. When you ENTER the time into the cell and delete it you lose both time & formula. may have been vba or format (?) I just know the little lady that posted it was given a slick way to do it. She ENTERED time wrong and it corected it to the proper time format.
Still searching.

Right click on the sheet name tab and select "view Code".

If there are just 2 lines there, delete the default information and paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
Application.EnableEvents = True
End If
End If
End Sub
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
Dave is our hero today. Thanks Dave!

The above formula needs some parentheses to keep it honest.

=(ROUNDDOWN(RC[-1],-2)/2400)+(MOD(RC[-1],100)/1440)
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
For the Truly Lazy:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim TempStr As String
If Not Intersect(Target, Range("A1:L40")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
TempStr = Target
If Len(TempStr)< 4 Then 'we need to pad the entry with leading "0"s
Beep

TempStr = String(4 - Len(TempStr), "0") & TempStr
Debug.Print TempStr
Else
Debug.Print TempStr
End If

Target = Left(TempStr, 2) & ":" & Right(TempStr, 2)
Application.EnableEvents = True
End If
End If
End Sub
This message was edited by stevebausch on 2002-09-02 09:45
 

cblincoln43

Board Regular
Joined
Mar 12, 2002
Messages
206
??????? Im confused! I can now enter 17:00 and the format changes it to 5:00pm and still calculates. In these codes i don't see the am, pm format. Also I just read chip pearson, date and time sheet and im going to see what i can mess up playing with it. Bob, Thanks PaddyD. Im still Searching.
 

Forum statistics

Threads
1,144,223
Messages
5,723,120
Members
422,478
Latest member
Rovan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top