Macro / function text time to 24hr excel time passed midnight fortotaling hr's

[Russmaz]

I am working on rosters
And because the rosters finish past midnight
I need to be able to let excel know this

To be able to calculate the times into total hours
Times entered into excel have to go in as 24hr clock but for
Excel to recognize times that are after midnight they are entered as
I.e. 24:00, 25:00, 26:00 to represent 12:00(midnight), 1:00am 2:00am
As on a 36hr clock,
These times are repozented as:-

412a are normal am
412p are afternoon PM
412x are after midnight Pm

When entered as 24hr clock cells are formatted to change to am/pm

So the problem is to change text time to Excel time over 24hr with a
macro and Function
The the Function that I am using with the macro, does not recognize
more than 24hr’s

Times to change text time to excel time
Time formats as below

Change 412a into Excel time 4:12
412p into Excel time 16:12
412x into Excel time 28:12

How can I ajust the function to achive this with the 'X'

+ Hrs = CInt(Hrs) + 24
does not work

The macro and function I am using is below
------------------------------------------------------------------
Function GetValue(s As String) As String

Dim Hrs As String

Dim Mins As String

If s <> "" Then

Do Until Len(s) = 5

s = "0" + s

Loop

Hrs = Left(s, 2)

Mins = Right(s, 3)

Mins = Left(Mins, 2)

If UCase(Right(s, 1)) = "A" Then

ElseIf UCase(Right(s, 1)) = "P" Then

If Hrs <> "12" Then

Hrs = CInt(Hrs) + 12

End If

End If

s = Hrs & ":" & Mins

End If

GetValue = s

End Function

 

[T. Valko]

I think you might be using a sledgehammer to kill an ant!

To calculate hours when time spans past midnight:

A1 = Start time = 3:00 PM or 15:00
B1 = End time = 1:00 AM or 1:00

=MOD(B1-A1,1)

Format as [h]:mm for a result of 10:00

Or, if you want the result as a decimal number:

=MOD(B1-A1,1)*24

Format as General or Number for a result of 10.


--
Biff
Microsoft Excel MVP


I am working on rosters
And because the rosters finish past midnight
I need to be able to let excel know this

To be able to calculate the times into total hours
Times entered into excel have to go in as 24hr clock but for
Excel to recognize times that are after midnight they are entered as
I.e. 24:00, 25:00, 26:00 to represent 12:00(midnight), 1:00am 2:00am
As on a 36hr clock,
These times are repozented as:-

412a are normal am
412p are afternoon PM
412x are after midnight Pm

When entered as 24hr clock cells are formatted to change to am/pm

So the problem is to change text time to Excel time over 24hr with a
macro and Function
The the Function that I am using with the macro, does not recognize
more than 24hr’s

Times to change text time to excel time
Time formats as below

Change 412a into Excel time 4:12
412p into Excel time 16:12
412x into Excel time 28:12

How can I ajust the function to achive this with the 'X'

+ Hrs = CInt(Hrs) + 24
does not work

The macro and function I am using is below
------------------------------------------------------------------
Function GetValue(s As String) As String

Dim Hrs As String

Dim Mins As String

If s <> "" Then

Do Until Len(s) = 5

s = "0" + s

Loop

Hrs = Left(s, 2)

Mins = Right(s, 3)

Mins = Left(Mins, 2)

If UCase(Right(s, 1)) = "A" Then

ElseIf UCase(Right(s, 1)) = "P" Then

If Hrs <> "12" Then

Hrs = CInt(Hrs) + 12

End If

End If

s = Hrs & ":" & Mins

End If

GetValue = s

End Function

 

[Shane Devenshire]

Hi,

If you still want a solution, here is a spreadsheet solution which does not
require a VBA macro or function. Just format the cell with a time format
afterwards:

=(LEFT(A1,LEN(A1)-3)&":"&MID(A1,LEN(A1)-2,2))+IF(RIGHT(A1)="p",0.5,1)