• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

When time is 12:00, it is always being displayed as 00:05

Tom A

Member
Hi,

I am formatting time into 24 hour format using a UDF function.

I input start and end times in two cells and they are converted to 24hour format for example 1pm to 13:00.

However, the time 12:00 always appears as 00:05 when I format it to hh:MM format in a different cell rather than remaining as that value.

If I enter 1pm or 2.30pm for example then they convert to 24 hour format fine.


Code:
Function GetWhen(DateCell As Date, MultipleDaysCell As String, EventStartTimeCell As String, EventEndTimeCell As String, ImportanceCell As Long) As String

Dim EventDate As Date

Dim EventTime As String

Dim When As String

'Get the start and end times

EventTime = "at "

If EventStartTimeCell = "" Then

EventTime = EventTime & "Time TBC"

Else

EventTime = EventTime & Format(EventStartTimeCell, "hh:mm")

End If

If EventEndTimeCell <> "" Then

EventTime = EventTime & " to " & Format(EventEndTimeCell, "hh:mm")

End If

GetWhen = When

End Function

thanks
 
Can you tell us what each parameter of the UDF should be?
eg: Datecell etc
a worked example would be great

Otherwise we have to guess as to the data structure and that may be causing errors
 
Can you tell us what each parameter of the UDF should be?
eg: Datecell etc
a worked example would be great

Otherwise we have to guess as to the data structure and that may be causing errors

I've not included the date cell code in the above code as that's working fine. I am using this function to generate a date and time to use with a database of upcoming events.
A sample output of the function is:
Thu 8 Oct at 14:00 to 16:00
The function passes this string onto another function to generate a complete event listing

DateCell = date which is formatted ddd m mmm
MultipleDaysCell = which is usually empty but if not then is used in place of the Date cell value i.e. Sun 4 Oct to Thu 8 Oct
EventStartTimeCell = a time that should be in 24 hour format i.e. 14:30 but if not then the function formats it into 24 hour format
EventEndTimeCell = the end time of an event which should be in 24 hour format but if not then converts it to 24 hour format. This string can have additional text at the end of it when it is passed to the function for example '16:00 (Repeat weekly)'
ImportanceCell = This is a value used to insert a '*' at the start of the complete generated string if it is an important event


I am getting 00:05 for both event start time and event end time
 
Thanks Vletm :) but I still get the same result even if I change the value for minutes from 'mm' to 'nn'

However, if I change the event start and end times to be a range rather than a string then it works as expected and outputs 12:00 when the time is 12:00.


I've had sometimes same kind of 'challenges' too.
If I remember right, one reason was mistakes to format minutes and months with same characters "mm". "mm" is for month.
Check link: http://www.excel-pratique.com/en/vba_tricks/date_format.php
I've had sometimes same kind of 'challenges' too.
If I remember right, one reason was mistakes to format minutes and months with same characters "mm". "mm" is for month.
Check link: http://www.excel-pratique.com/en/vba_tricks/date_format.php
 
Ooops ... just wondering
Code:
Function GetWhen( ...
    ....
    Dim When As String
    ...
    GetWhen = When
end Function
When do 'When' get some/any value?
 
... continue...
Have You checked how do 'EventStartTimeCell' and 'EventEndTimeCell' looks in that Function (with Watch and Break)?
Are those real values like Date with Time (05/12/2015 09:25:37) or what?
Anyway Time have to have be seconds too!
( If not, the result can be 'whatever'. )
And why String-types?
Sample-table please.
 
Both the start and end time cells have a custom format of 'hh:mm' and when the time is 12:00, it is storing it as 0.5

I have a list of upcoming events and I'm joining together the date, start and end times + details of the event etc into an event listing in a separate cell.

... continue...
Have You checked how do 'EventStartTimeCell' and 'EventEndTimeCell' looks in that Function (with Watch and Break)?
Are those real values like Date with Time (05/12/2015 09:25:37) or what?
Anyway Time have to have be seconds too!
( If not, the result can be 'whatever'. )
And why String-types?
Sample-table please.
 
Check this!
There are two versions with a sample sheet.
The lower is better 'GetWhen'-function.
Code:
Function GetWhen(DateCell, MultipleDaysCell, EventStartTimeCell, EventEndTimeCell, ImportanceCell) As String
    EventTime = "at "
    If EventStartTimeCell = "" Then
        EventTime = EventTime & "Time TBC"
    Else
        EventTime = EventTime & Format(EventStartTimeCell, "hh:mm")
    End If
    If EventEndTimeCell <> "" Then
        EventTime = EventTime & " to " & Format(EventEndTimeCell, "hh:mm")
    End If
    GetWhen = EventTime
End Function

Sub test()
    With Worksheets("Sheet1")
        .Range("H2") = GetWhen(.Range("B2"), .Range("C2"), .Range("D2"), .Range("E2"), .Range("F2"))
        .Range("H3") = Get_When(.Range("D2"), .Range("E2"))
    End With
End Sub

Function Get_When(EventStartTimeCell, EventEndTimeCell) As String
    EventTime = "at "
    If EventStartTimeCell = "" Then
        EventTime = EventTime & "Time TBC"
    Else
        EventTime = EventTime & Format(EventStartTimeCell, "hh:mm")
    End If
    If EventEndTimeCell <> "" Then
        EventTime = EventTime & " to " & Format(EventEndTimeCell, "hh:mm")
    End If
    Get_When = EventTime
End Function
Questions?
Sample TAB.png
 
Thanks vletm. The last message I posted on here doesn't seem to have posted. The event start and end time cells are formatted with a custom format of 'hh:mm' which is the problem. The value for 12:00 shows as 0.5. Changing them from a string to a range when I feed them into the GetWhen formula is working.


Check this!
There are two versions with a sample sheet.
The lower is better 'GetWhen'-function.
Code:
Function GetWhen(DateCell, MultipleDaysCell, EventStartTimeCell, EventEndTimeCell, ImportanceCell) As String
    EventTime = "at "
    If EventStartTimeCell = "" Then
        EventTime = EventTime & "Time TBC"
    Else
        EventTime = EventTime & Format(EventStartTimeCell, "hh:mm")
    End If
    If EventEndTimeCell <> "" Then
        EventTime = EventTime & " to " & Format(EventEndTimeCell, "hh:mm")
    End If
    GetWhen = EventTime
End Function

Sub test()
    With Worksheets("Sheet1")
        .Range("H2") = GetWhen(.Range("B2"), .Range("C2"), .Range("D2"), .Range("E2"), .Range("F2"))
        .Range("H3") = Get_When(.Range("D2"), .Range("E2"))
    End With
End Sub

Function Get_When(EventStartTimeCell, EventEndTimeCell) As String
    EventTime = "at "
    If EventStartTimeCell = "" Then
        EventTime = EventTime & "Time TBC"
    Else
        EventTime = EventTime & Format(EventStartTimeCell, "hh:mm")
    End If
    If EventEndTimeCell <> "" Then
        EventTime = EventTime & " to " & Format(EventEndTimeCell, "hh:mm")
    End If
    Get_When = EventTime
End Function
Questions?
View attachment 23103
 
Back
Top