Tricky VBA function

[jmulroy6]

Have some pretty basic data, just a college timetable with times going down in 1 column and room numbers across the way in 1 row.
I want to create a function that given a time as a string (eg "9am"), returns a time in the twenty four hour clock (eg 0900).
Need help.

 

[tusharm]

Tricky? Not really. {grin}

Option Explicit

Function VBATimeValue(X)
    VBATimeValue = TimeValue(X)
    End Function

Use it as =VBATimeValue(K9) where K9 contains the time as 9am or 9:30:30pm

 

[tusharm]

I forgot to mention. Format the cell with the formula as Custom with the custom format hhmmss (or just hhmm if that is what you want).

 

[jmulroy6]

Cheers,
I want to as well convert all the data in the workbook, (which has the column headings as rooms and times along the side, classes are then given spaces) into a different workbook organised in a sheet for each room and with the 4 levels, i.e Foundation, Cert, Degree and Pro (the old sheet names) as column headings.

 

[jmulroy6]

'
Range("A1:B11").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Range("B37").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "470"
Range("B1").Select
Windows("Timetable.xls").Activate
Windows("Book3").Activate
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Foundation"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Degree"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Cert"

Range("E1").Select
ActiveCell.FormulaR1C1 = "Professional"


Thats the code i'm using to add a new WB and WS with new columns, do i need to work through the other 13 like that