Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Number Format
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Number Format
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 -- Gary''s Student - gsnu200750 "CNB Cheryl" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Number Format
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 \CNB Cheryl wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Number Format
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Number Format
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "CNB Cheryl" <CNB wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Number Format
Now why didn't I think of that?<g
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... 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 "Ron Rosenfeld" wrote in message ... On Tue, 16 Oct 2007 12:14:01 -0700, CNB Cheryl <CNB wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Number Format
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 "CNB Cheryl" <CNB wrote in message ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Number Format
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Number Format
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Number Format
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) In article , "·!¦[··ï¡†¡ï·»Gen°Digger«·ï¡†¡ï··]¦!·" wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Number Format
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 "JE McGimpsey" wrote in message ... 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) In article , "·!¦[··ï¡†¡ï·»Gen°Digger«·ï¡†¡ï··]¦!·" wrote: 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Number Format
=((E8<D8)+E8-D8+(G8<F8)+G8-F8)*24 and format as number or general.
-- David Biddulph "·!¦[··ï¡?¡ï·»Gen°Digger«·ï¡?¡ï··]¦!·" wrote in message . .. 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 "JE McGimpsey" wrote in message ... 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) In article , "·!¦[··ï¡?¡ï·»Gen°Digger«·ï¡?¡ï··]¦!·" wrote: 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Number Format
David,
Thank you... this resolves a LOT of headaches for me... thank you so much!!! :oD Terrae "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =((E8<D8)+E8-D8+(G8<F8)+G8-F8)*24 and format as number or general. -- David Biddulph "·!¦[··ï¡?¡ï·»Gen°Digger«·ï¡?¡ï··]¦!·" wrote in message . .. 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 "JE McGimpsey" wrote in message ... 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) In article , "·!¦[··ï¡?¡ï·»Gen°Digger«·ï¡?¡ï··]¦!·" wrote: 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Number Format
An alternative:
=MOD(E8-D8+G8-F8,1)*24 In article , "·!¦[··ï¡†¡ï·»Gen°Digger«·ï¡†¡ï··]¦!·" wrote: 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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Number Format
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! :o) Terrae "JE McGimpsey" wrote in message ... An alternative: =MOD(E8-D8+G8-F8,1)*24 In article , "·!¦[··ï¡†¡ï·»Gen°Digger«·ï¡†¡ï··]¦!·" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving custom number format to NUMBER | Setting up and Configuration of Excel | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions | |||
Custom Number Format | Excel Discussion (Misc queries) | |||
Custom number format always defaults last number to 0. | Excel Discussion (Misc queries) |