Converting base 10 to base 6

**Zyphon** · 02-22-2008, 12:14 PM

Hi Guys, is it possible to convert base 10 into base 6 without formatting a cell to "Time"?

What I mean is say for instance Cell A2 had the start time of 9.30 instead of 9:30 and cell B2 had the end time of 10.45 instead of 10:45. Now cell C2 should subtract the end time from the start time and come up with an answer of 1:15 or even 1.15.

I know I could do all this by formating the cells to Time Formatting but this speadsheet is for the lay person who usually enter time values using the decimal point instead of the colon.

Any ideas?

Thanks.

**MickG** · 02-22-2008, 01:24 PM

Hi,Try this to start.
Place your "from" times in Column "A" and your "to" Times in Column "B".
Run the code, the resulting time differences will be in column "C". and a Msgbox.
If you don't want the Msgbox, delete it from the code.

Make sure columns "A" & "B" are formatted as numbers, and Column "C" as Time
If you want it altered let me know

Dim cl As Range, TT
Dim aTl, aTr, bTl, bTr, oSa, oSb, Ts, sTr, sTl

For Each cl In Range(Range("b1"), Range("b" & Rows.Count).End(xlUp))
 aTl = Split(Format(cl.Offset(0, -1), "0.00"), ".")(0)
  aTr = (Split(Format(cl.Offset(0, -1), "0.00"), ".")(1)) / 60
   bTl = Split(Format(cl.Value, "0.00"), ".")(0)
    bTr = (Split(Format(cl.Value, "0.00"), ".")(1)) / 60
     oSa = aTl + aTr
      oSb = bTl + bTr
       Ts = oSb - oSa
        sTr = Split(Format(Ts, "0.00"), ".")(0)
  If sTr < 10 Then sTr = Right(sTr, 1)
        sTl = ("." & (Split(Format(Ts, "0.00"), ".")(1))) * 60

         TT = sTr & ":" & sTl
          cl.Offset(0, 1).Value = TT
            MsgBox TT
Next

Regards Mick

**shg** · 02-22-2008, 01:37 PM

If you want the result as 1.15, then =DOLLARFR( DOLLARDE(B2, 60) - DOLLARDE(A2, 60), 60)

If you want 1:15, then = (DOLLARDE(B2, 60) - DOLLARDE(A2, 60) ) / 24 and format as [h]:mm

**MickG** · 02-22-2008, 03:02 PM

Hi, Or better still here is a User Defined Function.
=+DecTime("To Time","From Time")
Open the sheet, Right click the "Tab", select "View Code". -- VB Window Appears.
Click "Insert", "Module".
Paste the code below, into the right hand window.
Close VB Editor.
Insert Function in cell, Add "To" and "From" cell references
Click "Return"

Function DecTime(dtF As String, dtS As String)
Dim cl As Range, TT
Dim aTl, aTr, bTl, bTr, oSa, oSb, Ts, sTr, sTl

 aTl = Split(Format(dtS, "0.00"), ".")(0)
  aTr = (Split(Format(dtS, "0.00"), ".")(1)) / 60
   bTl = Split(Format(dtF, "0.00"), ".")(0)
    bTr = (Split(Format(dtF, "0.00"), ".")(1)) / 60
     oSa = aTl + aTr
      oSb = bTl + bTr
       Ts = oSb - oSa
        sTr = Split(Format(Ts, "0.00"), ".")(0)
  If sTr < 10 Then sTr = Right(sTr, 1)
        sTl = ("." & (Split(Format(Ts, "0.00"), ".")(1))) * 60

         TT = sTr & ":" & sTl
          DecTime = TT

End Function

Regards Mick

**daddylonglegs** · 02-22-2008, 06:09 PM

Assuming your time period will be within one day try this for 1:15

=TEXT(B2*100,"0\:00")-TEXT(A2*100,"0\:00")

or for 1.15

=B2-A2-0.4*(MOD(B2,1)<MOD(A2,1))