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

Please Login or Register  to view this content.

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"

Please Login or Register  to view this content.

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))

**MickG** · 02-23-2008, 08:39 AM

The words "Wood" and "Trees" comes to mind, but there again I've learnt something about "mso number formats"
Mick

**Zyphon** · 02-24-2008, 04:27 PM

Sorry for the late response guys been away all weekend.

I would like to say a big thanks to you all for all the various ways to achieve me goal for my problem.

@MickG

I shall try your code it sounds excellent I shall feedback once I have tried it.

Thanks again guys.

**shg** · 02-24-2008, 05:49 PM

Here's another option for a code-based solution to return an Excel time:

Please Login or Register  to view this content.

DLL's solution is equally applicable to VBA:

Please Login or Register  to view this content.

**Zyphon** · 02-29-2008, 04:55 AM

@shg

Thank you very much for your code also. I have not had the time to test these out yet but I will do so this weekend. I like DLL method also as it gives me the option to use a formulae based solution as well as a VBA one. It is always nice to have two methods for completing my goal.

Thanks again everyone I shall play around with these methods.

Converting base 10 to base 6

LinkBack

Thread Tools

Rate This Thread

Display

Converting base 10 to base 6

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions