Time/hour question

[bowelripper]

Hello!

I have a couple of questions concerning the use of time/hours in Excel 2007.

Firstly, it seems I have to use a colon for the time format. In other words 7 in the morning must be entered as 07:00 in a cell, but in Norway where I live the correct format is 07.00 (alternatively 0700). If I try the latter it just gets messed up. Is it possible to work with the latter format? How? (By the way, we use the 24-hour system here.)

Secondly, I have a formula where I need to state that if a certain hour in a cell is less than /earlier than 07:00 it should calculate a value. Example where let's say B8 equals 06:00: =IF(B8<07:00;(07:00-B8)*24;0). The problem is that Excel won't accept 07:00 as a value in the formula bar, so how can I reformulate this?

Thanks,
BR

 

[bowelripper]

You do not have to convert anything VBA is already part of Excel. Open up Excel and select Alt + F11 and this will open the VBA editor (VBE) and this is where you enter code.

Alright... I have never used it before so do you have any idea what to do?

 

[Scott Huish]

You do not have to convert anything

Yes, you do. You have to convert the time entry format desired into a format that Excel understands.

What range are you entering your times into?

 

[Scott Huish]

This is written for Column A, please let me know if your range is different:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("A:A"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If IsNumeric(c) And c <> "" Then
            c = TimeValue(Replace(c, ".", ":"))
            c.NumberFormat = "hh.mm"
        End If
    Next
Application.EnableEvents = True
End Sub

To put this in:
Copy the code above.
Right click on the sheet tab for the sheet you want this to happen on.
Click on View Code.
Paste into White area.
Hit Alt-q
This code will be saved when you save the workbook (XL2007 Note: You need to save as a macro enabled workbook .xlsm)

 

[bowelripper]

It's not for column A, but for columns B and C. Thanks!

 

[Scott Huish]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("B:C"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If IsNumeric(c) And c <> "" Then
            c = TimeValue(Replace(c, ".", ":"))
            c.NumberFormat = "hh.mm"
        End If
    Next
Application.EnableEvents = True
End Sub

 

[bowelripper]

Hmm, the hours are still with a colon after I followed your instructions.