Custom Number Format

[CNB Cheryl]

I need to have a 16 digit number with - between each number. I created a
custom format, however the 16th digit always reverts to 0 (zero). I need
this to accept all the numbers that are entered into the field. We are on a
deadline so any help is appreciated. I have tried comma's and other items,
but no go...

Thanks
CNBCheryl

 

[Gary''s Student]

Rather than Cusstom Format, format the cell as tet and enter the - manually:

1-2-3-4-5-6-7-8-9-0-1-2-3-4-5-6

 

[Beege]

Cheryl,

Excel will truncate(?) numbers longer than 15 digits, as you've seen.
Best to format as text. I'm sure someone will come up with a VBA
solution for you, but it's still text. Access provides a "mask" that
would put in the dashes automatically, but Excel won't.

Beege

 

[Ron Rosenfeld]

On Tue, 16 Oct 2007 12:14:01 -0700, CNB Cheryl <CNB

I need to have a 16 digit number with - between each number. I created a
custom format, however the 16th digit always reverts to 0 (zero). I need
this to accept all the numbers that are entered into the field. We are on a
deadline so any help is appreciated. I have tried comma's and other items,
but no go...

Thanks
CNBCheryl

Excel's specifications are such that you cannot enter a 16 digit *number*. You
can only enter the value as text.

Since it is a pain to enter the text with the hyphens, I would suggest

1. Preformat the data entry fields as TEXT.
2. Enter your 16 digit number.
3. Run this UDF
=InsHyphens(cell_ref)

To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens:

==========================================
Option Explicit
Function InsHyphens(str As String)
Dim i As Long
Dim Temp(15)
Dim s As String

For i = 0 To 15
s = Mid(str, i + 1, 1)
If IsNumeric(s) Then
Temp(i) = CLng(s)
Else
InsHyphens = CVErr(xlErrValue)
Exit Function
End If
Next i

InsHyphens = Join(Temp, "-")
End Function
========================================

As written, the code will give valid output if your string contains 16 or more
digits. It will give a #VALUE! error if you have fewer than 16 digits, or if
any character of the first 16 is not a digit.


You don't have to use a UDF, you could write a formula of the type:

=LEFT(A1,1)&"-"&
MID(A1,2,1)&"-" &
MID(A1,3,1) & "-" &
....

and so forth until you have handled all 16 digits.

It was easier for me to write the UDF.

--ron

 

[RagDyer]

If you're trying to eliminate the need to key in all those dashes, you can
try a text formula in an adjoining column that will display the data as you
wish.

It's a *long and cumbersome* formula, but doing it once and copying the
formula containing column for future use may be a viable option for you, as
opposed to having to enter all those dashes.

Enter the original values in Column A, *preceded* with an apostrophe.
This makes the entry text, and allows XL to *accurately* display the 16th
digit.

Then in B1 enter this formula:

=LEFT(A1)&"-"&MID(A1,2,1)&"-"&MID(A1,3,1)&"-"&MID(A1,4,1)&"-"&MID(A1,5,1)&"-"&MID(A1,6,1)
&"-"&MID(A1,7,1)&"-"&MID(A1,8,1)&"-"&MID(A1,9,1)&"-"&MID(A1,10,1)&"-"&MID(A1,11,1)
&"-"&MID(A1,12,1)&"-"&MID(A1,13,1)&"-"&MID(A1,14,1)&"-"&MID(A1,15,1)&"-"&MID(A1,16,1)

Copy down as needed.

 

[Peo Sjoblom]

Or format as text, enter the string and then use a help column and something
like

=TEXT(LEFT(A1,15),"0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-")&RIGHT(A1)


and copy down



--


Regards,


Peo Sjoblom

 

[RagDyer]

Now why didn't I think of that?<g>

 

[Rick Rothstein \(MVP - VB\)]

I am assuming you want the 16-digit "number" you enter to be changed within
the cell it was entered in. There are two possibilities to your request;
both assume the cells are formatted as Text (which, as has been pointed out,
is required so as not to lose the 16th digit)...

1) You already entered the numbers and want to change them. If this is the
case, you can select those already filled in cells and run this macro...

Public Sub InsertDashes()
Dim C As Range
For Each C In Selection
If C.Value Like "################" Then
C.Value = Format(C.Value, "@-@-@-@-@-@-@-@-@-@-@-@-@-@-@-@")
End If
Next
End Sub


2) You want to type in the 16-digit number and have it change to the format
you want when you enter it. Add this Worksheet Change event to the code
window for the sheet where you will be entering your "numbers" (right-click
on that sheet's tab and select View Code from the popup menu)...

Private Sub Worksheet_Change(ByVal Target As Range)
Const ColumnToChange As String = "A"
If Target.Column = Asc(ColumnToChange) - 64 Then
If Target.Value Like "################" Then
Application.EnableEvents = False
Target.Value = Format(Target.Value, "@-@-@-@-@-@-@-@-@-@-@-@-@-@-@-@")
Application.EnableEvents = True
End If
End If
End Sub


Rick

 

[~Gen*Digger~]

I am new to the group and am trying to find a way to have a formula
displayed as time display as a decimal number (3 decimal place). I am
trying to create a biweekly timesheet that displays the time added. I work
graveyard, so the hours run from one date into the next day, which would
make the hour format as such: [h]:mm. My problem is that when I add up the
hours, they are displayed in a time format instead of in a decimal format,
e.g.: 8:33 [h]:mm instead of 8.967 hours.minutes... can someone help me
with this, please? I've been trying to figure this out for 2 days now and
have scoured the 'Net for help... I'm not that great with spreadsheets, so
layman's terms would be of great help... thank you in advance for your
help...

Terrae
(e-mail address removed)

 

[·!¦[··ï¡†¡ï·»Gen°Digger«·ï¡†¡ï··]¦!·]

What I currently have:
In Out In Out Total
Hours
10:45 AM 7:18 PM 8:33
11:00 AM 7:01 PM 8:01


What I want to achieve:
In Out In Out Total
Hours
10:45 AM 7:18 PM 8.567
11:00 AM 7:01 PM 8.017


I wanted to repost this so that those trying to understand what I want can
see what I have, compared to what I want to get... sorry for the repost.

I am new to the group and am trying to find a way to have a formula
displayed as time display as a decimal number (3 decimal place). I am
trying to create a biweekly timesheet that displays the time added. I work
graveyard, so the hours run from one date into the next day, which would
make the hour format as such: [h]:mm. My problem is that when I add up the
hours, they are displayed in a time format instead of in a decimal format,
e.g.: 8:33 [h]:mm instead of 8.967 hours.minutes... can someone help me
with this, please? I've been trying to figure this out for 2 days now and
have scoured the 'Net for help... I'm not that great with spreadsheets, so
layman's terms would be of great help... thank you in advance for your
help...

Terrae
(e-mail address removed)

 

[JE McGimpsey]

Times are stored as fractional days, so to get integer/fractional hours,
multiply by 24:

C2: =(B2-A2)*24

or, to get exactly 3 decimal places:


C2: =ROUND((B2-A2)*24,3)

 

[·!¦[··ï¡†¡ï·»Gen°Digger«·ï¡†¡ï··]¦!·]

Thank you for your reply... I understand the concept you are trying to
convey, but maybe if I give you the formula I currently have, you can help
me figure out how to rework the formula to get it to do what I am trying to
achieve:

=(E8<D8)+E8-D8+(G8<F8)+G8-F8

This formula is where I get 8:33 (in [h]:mm format) and where I would like
to have the decimal format... can do?

Thank you... again... in advance for you kind assistance, I appreciate it.

Terrae

Times are stored as fractional days, so to get integer/fractional hours,
multiply by 24:

C2: =(B2-A2)*24

or, to get exactly 3 decimal places:


C2: =ROUND((B2-A2)*24,3)

What I currently have:
In Out In Out Total
Hours
10:45 AM 7:18 PM 8:33
11:00 AM 7:01 PM 8:01


What I want to achieve:
In Out In Out Total
Hours
10:45 AM 7:18 PM 8.567
11:00 AM 7:01 PM 8.017


I wanted to repost this so that those trying to understand what I want
can
see what I have, compared to what I want to get... sorry for the repost.

I am new to the group and am trying to find a way to have a formula
displayed as time display as a decimal number (3 decimal place). I am
trying to create a biweekly timesheet that displays the time added. I
work
graveyard, so the hours run from one date into the next day, which would
make the hour format as such: [h]:mm. My problem is that when I add up
the
hours, they are displayed in a time format instead of in a decimal
format,
e.g.: 8:33 [h]:mm instead of 8.967 hours.minutes... can someone help me
with this, please? I've been trying to figure this out for 2 days now
and
have scoured the 'Net for help... I'm not that great with spreadsheets,
so
layman's terms would be of great help... thank you in advance for your
help...

Terrae
(e-mail address removed)

 

[David Biddulph]

=((E8<D8)+E8-D8+(G8<F8)+G8-F8)*24 and format as number or general.
--
David Biddulph

Thank you for your reply... I understand the concept you are trying to
convey, but maybe if I give you the formula I currently have, you can help
me figure out how to rework the formula to get it to do what I am trying
to achieve:

=(E8<D8)+E8-D8+(G8<F8)+G8-F8

This formula is where I get 8:33 (in [h]:mm format) and where I would like
to have the decimal format... can do?

Thank you... again... in advance for you kind assistance, I appreciate it.

Terrae

Times are stored as fractional days, so to get integer/fractional hours,
multiply by 24:

C2: =(B2-A2)*24

or, to get exactly 3 decimal places:


C2: =ROUND((B2-A2)*24,3)

What I currently have:
In Out In Out Total
Hours
10:45 AM 7:18 PM 8:33
11:00 AM 7:01 PM 8:01


What I want to achieve:
In Out In Out Total
Hours
10:45 AM 7:18 PM 8.567
11:00 AM 7:01 PM 8.017


I wanted to repost this so that those trying to understand what I want
can
see what I have, compared to what I want to get... sorry for the repost.

I am new to the group and am trying to find a way to have a formula
displayed as time display as a decimal number (3 decimal place). I am
trying to create a biweekly timesheet that displays the time added. I
work
graveyard, so the hours run from one date into the next day, which would
make the hour format as such: [h]:mm. My problem is that when I add up
the
hours, they are displayed in a time format instead of in a decimal
format,
e.g.: 8:33 [h]:mm instead of 8.967 hours.minutes... can someone help me
with this, please? I've been trying to figure this out for 2 days now
and
have scoured the 'Net for help... I'm not that great with spreadsheets,
so
layman's terms would be of great help... thank you in advance for your
help...

Terrae
(e-mail address removed)

 

[·!¦[··ï¡†¡ï·»Gen°Digger«·ï¡†¡ï··]¦!·]

David,

Thank you... this resolves a LOT of headaches for me... thank you so much!!!
D

Terrae

=((E8<D8)+E8-D8+(G8<F8)+G8-F8)*24 and format as number or general.
--
David Biddulph

Thank you for your reply... I understand the concept you are trying to
convey, but maybe if I give you the formula I currently have, you can
help me figure out how to rework the formula to get it to do what I am
trying to achieve:

=(E8<D8)+E8-D8+(G8<F8)+G8-F8

This formula is where I get 8:33 (in [h]:mm format) and where I would
like to have the decimal format... can do?

Thank you... again... in advance for you kind assistance, I appreciate
it.

Terrae

Times are stored as fractional days, so to get integer/fractional hours,
multiply by 24:

C2: =(B2-A2)*24

or, to get exactly 3 decimal places:


C2: =ROUND((B2-A2)*24,3)

What I currently have:
In Out In Out Total
Hours
10:45 AM 7:18 PM 8:33
11:00 AM 7:01 PM 8:01


What I want to achieve:
In Out In Out Total
Hours
10:45 AM 7:18 PM 8.567
11:00 AM 7:01 PM 8.017


I wanted to repost this so that those trying to understand what I want
can
see what I have, compared to what I want to get... sorry for the
repost.

I am new to the group and am trying to find a way to have a formula
displayed as time display as a decimal number (3 decimal place). I am
trying to create a biweekly timesheet that displays the time added. I
work
graveyard, so the hours run from one date into the next day, which
would
make the hour format as such: [h]:mm. My problem is that when I add up
the
hours, they are displayed in a time format instead of in a decimal
format,
e.g.: 8:33 [h]:mm instead of 8.967 hours.minutes... can someone help
me
with this, please? I've been trying to figure this out for 2 days now
and
have scoured the 'Net for help... I'm not that great with spreadsheets,
so
layman's terms would be of great help... thank you in advance for your
help...

Terrae
(e-mail address removed)

 

[JE McGimpsey]

An alternative:

=MOD(E8-D8+G8-F8,1)*24

 

[·!¦[··ï¡†¡ï·»Gen°Digger«·ï¡†¡ï··]¦!·]

I'm sorry, I don't know your name, but thank you... a TON... this actually
was more accurate in what I was trying to achieve. Since I work graveyard,
my hours bleed into the next day and try as I may, I could not get the hours
to reflect the way I needed them to. I am doing this to use as an auditing
tool so that I can audit the printouts I get back from my referral agency (I
work for myself).

Thanks so much... aaahhh... this was driving me crazy! )

Terrae

An alternative:

=MOD(E8-D8+G8-F8,1)*24

Thank you for your reply... I understand the concept you are trying to
convey, but maybe if I give you the formula I currently have, you can
help
me figure out how to rework the formula to get it to do what I am trying
to
achieve:

=(E8<D8)+E8-D8+(G8<F8)+G8-F8

This formula is where I get 8:33 (in [h]:mm format) and where I would
like
to have the decimal format... can do?

Thank you... again... in advance for you kind assistance, I appreciate
it.