Excel cells changes number format automatically
I am using Excel 2007. Many of the cells I am working on are in general
format where I store various numbers for analysis. These values change itself
to date format occassionally creating whole lot of work for me to change them
back to general format. How can I solve this issue?
Also in my pivot table, my source data is in general format. Still when I
click on sub total in any catagory in the table to find details about it,
formats are changed date format in the new sheet it pops up.
Can someone help resolve this issue.
...Custom Number Format problem
I need a special number format, but I can't seem to get what I want:
IF a number is an integer, then there should be NO decimal point
IF a number has a decimal part, then if should display only one digit
3 -> 3
4.34 -> 4.3
2.88 -> 2.9
34 -> 34
5.01 -> 5.0
Is it possible to do this with a custom number format ?
P.S. I would prefer NOT to use any VBA to do this!
But easier would be:
and format as General
Of course you can replace A1 with your original formula, but then it is cal...Two Condition Vlookup
I have two worksheets that contain similar information. What I need is a
formula that will lookup the Value of column A in worksheet one, but also
there’s a second condition that I want it to meet which is in column B. So
basically if these two conditions are met then I want to see the result from
using worksheet 2 as the lookup reference table. Does anyone know the correct
format for a two condition Vlookup? Any help is greatly appreciated.
You have a few choices...
One is to insert a new column A in the rates worksheet.
Then you could concatenate the values in the new colu...Formatting times
How do I format a column so that it shows
when either hours or tenths may not be part of the data input?
I'd also prefer not to have zero for the hours or tenths if they're not
And why does the "input field" at the top of the screen show the number as a
time even if I've formatted the cell as one of the other "time" choices?
That going to be hard using the Format Cells dialog box but suppose your
times are starting in A1 then the following formula will do what you want:
I have a table that contains values that are in a very unique format. The
decimal and comma are incorrect...(Pls don't ask why, file is from Europe...)
The decimal should be a comma and vice versa.
Anyway, I need to know how to format (in a query) in such a way that if
reflects correct currency.
Currently: Should Look Like:
Can anyone help?
I would try this:
Select the range to fix
what: . (dot)
T...40 Col Receipt Format
I would like to create a receipt format which places the item code on a line,
then drops a line and print the item description (my shop's item descriptions
are quiote long). Can anybody help and also point me to where I can learn
more about changing the receipt formats in more advanced ways such as add
additional comment lines etc?
Go to receipt.xml and find the "Transaction Detail" block there you can
replace the whole block with following (then attached in so manager again the
receipt format) this sometime require as it will not apply automatically the
...Paste format with Freeze Frame into an new worksheet
How can I take a spreadsheet from one worksheet and paste to a new worksheet
but keep all of my columns, rows, and freeze frame set when I paste into the
new worksheet? An exact copy into a new worksheet?
Instead of copy/pasting the cells, why not copy the sheet? Right-click the
sheet tab, Move or Copy. You can specify New in the To Book box. Be sure to
click "Create a copy" or it'll move the sheet out of the original workbook.
That box should be at the top of the dialog.
"Celeste" <Celeste@discussions.microsoft...How do I keep formatting when creating a chart from a pivot?
I have a data source that I use to create various pivots and the I use thos
pivots to create graphs. I replace the detail for the pivots each month and
refreash each pivot which updates the graphs. The problem is the column and
data values lose their formatting when I refreash the pivots. Is there a way
to make the formatting of the graph stay?
Hi, this is a well-known nuisance. One workaround is to record a macro
while you do the formatting and then run the macro after you refresh the
I have a data source that ...Formatting cells in Excel
Help! My ability to format a cell in Excell has suddenly disappeared! Any
date I input will only show the serial number format and I cannot get it to
change. What can I do?
Sounds like you are in "View Formula" mode.
Hit CTRL + `(above Tab key) to toggle off/on.
Gord Dibben MS Excel MVP
On Fri, 16 Jun 2006 11:30:02 -0700, jdcc63 <firstname.lastname@example.org>
>Help! My ability to format a cell in Excell has suddenly disappeared! Any
>date I input will only show the serial number format and I cannot get it to
>change. What can I do?
Gord --...Excel with Concatenate does not format date correctly
I am using Excel 2007. I want to use concatenate to combine employee
name with date started.
The date is not showing correctly.
The screen shows:
What do I need to do differently, so it says:
> I am using Excel 2007. I want to use concatenate to combine employee
> name with date started.
> The date is not showing correctly.
> A1=Jane Doe
> The screen shows:
> Jane ...How do I add a zero in front of a number in general format?
When I type a number in the "General" format with a zero in front of the
numbers and move to the next cell, the zero disappears. I want the zero to
be in front of the number.
How do I fix this?
Format the range with the required number of zeroes, such as 000.
(remove nothere from the email address if mailing direct)
"dvs" <email@example.com> wrote in message
> When I type a number in the "General" format with a zero in front of the
> numbers and move to the nex...Email report in PDF Format using a Macro
Using a macro I can generate and export files in pdf format. Then I have to
create an email, attach the report and send. Is there a way to email a report
in pdf format directly from Access utilizing a macro?
I don't have an answer for you but a question. I am trying to do what you
have accomplised but don't know how.
> Using a macro I can generate and export files in pdf format. Then I have to
> create an email, attach the report and send. Is there a way to email a report
> in pdf format directly from Access utilizing a macro?
Once ...CRM 3.0
On the Order form we have created a new attribute called CCExpiryDate and
while creating it you only get the formatting options of Date or DateTime,
and there does not seem to be a way to change the format of a field, like in
say Access or Excel to display or even allow entry of MM/yy. It is only
dd/MM/yy. Is there any way to change how it can be entered or even the
It's not possible to change the input or display format, unless you're
mimic the behavior of the CRM date cont...Can I change from a poster setting to a smaller format?
Select File, Page setup and select the size you want.
"lisa" <firstname.lastname@example.org> wrote in message
...Formating Fiscal Quarters, rather than Months
I would like to be able to format a date based upon fiscal quarter, rather
than simply Month year. This would be based upon the underlining data: For
example, what I would like to see:
Q1FY06 Q2FY06 Q3FY06 Q4FY06
Would be created by inputing:
11/1/2005 02/01/2005 05/01/2005 08/01/2005
Once formated they would show as above. Otherwise, I will define a named
field, and go that way, but I have a lot of these headings to do, and
formatting them would be simplest way to get them to work, if possible.
Is there any sort of custom formatting that would do this?
PatK...Format Background Color Teaser
lo to med. important, not sure. When using "Fill Color", or other method you
may suggest, Is there a way to make a default background color, that can be
changed temporarily / goes back to original color if clearing "fill color".
(e.g. if base color 25% grey, then red, yellow or light orange). text may or
may not be present in cell. Is there an entry for Custom Background Colors??
/ will that work, danke schone in advance -Nastech
conditional format (insuf): row()
conditional format (sloppy): =OR(LEFT(F8,2)="b ",LEFT(F8,3)="bb ")
...Format Records alternating colours.
I would like to have aternating rows differnet colours.
Can you tell excel to do this?
Insert a blank (helper) column before your data.
(I'll assume it will be Col_A. Col_B will list customers)
Copy A2 down as far as you need
Select from B2 down and to the right as far as you have data
...with B2 as the active cell.
From the Excel Main Menu:
Formula is: =$A2
Click the [Format] button
....select the Re...Conditional Formatting #100
I am not sure if this is possible but I have a spreadsheet with two columns
of dates. If the date in one column is less than the one in the other column
I want to highlight the column in a different colour. However the dates are
Therefore I have tried to put an if statement in the conditional formatting
but it gives me an error. I could create a separate IF statement column but
this becomes a bit clumsy (there's actually loads of dates on different
Therefore in summary I want t...Date Format 04-28-10
I have a spreadsheet that contains a list of dates but they are formatted as
follows - 20100427. How would I go about converting those cells to a
standard format - 04/27/2010? Any help or suggestions would be appreciated.
Use a formula such as this to convert it to a date
=DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2))
you can then format the date however you wish using standard XL formats.
> I have a spreadsheet that contains a list of dates but they are formatted as
> follows - 20100427. How would I g...simple format question
i have the following entered as the format for a text box:
(000) 000-0000" x"000
but am getting a display that looks like this:
(000) 212-6757 x760
when it should be:
The field has number data type and the last 3 digits (the phone number
extension) is not always included (I suspect that i may need to break the
extension out to a seperate field (?)
any thoughts would be greatly appreciated
Mark Kubicki wrote:
>i have the following entered as the format for a text box:
>(000) 000-0000" x"000
>but am ...Inbox file in OE changed to .dbt format
Help: I can't access one of my inboxes in OE. All of a
sudden it dissappeared-- so I went to the identities
folder and found that it was there but had been changed
into an Outlook file with a .dbt extension. How do I
change it back so I can still use it in OE?
Any help will be greatly appreciated,
Hi, Rob -
This is a group to support Outlook from the Office group of programs.
Outlook Express is a part of Internet Explorer and is a quite different
program, despite its similar name..
You will probably get a faster and more expert answer if you post this to an
O...Cells with time format and calculating the diffrence
I have formatted 2 cells say A1 and B1 as time and chose the first type
being in the form 13:30 - This shows up in the equation line as 1:12:00 PM.
So I type in 13:12 and I get what is required BUT is there a way that I can
type 1312 without having to put the ':' in? and still get it to produce the
time equation format for my 3 rd cell C1 which will be to calculate the
minutes difference between the 2 first cells eg. 13:12 and 13:20 giving the
3rd cell as 0:08 . I have used =+B1-A1 for the C1 cell.
Also the other thing is that when I wish to calculate the minutes between
the 2...Pivot table Format Style Sheet Capability
I have been using Excel pivot tables for a number of years. It is a great
capability. One feature that I would really like to see is the ability to
modify the Auto Format style sheets. For example it would be nice to change
the color scheme or the font used on one of the Auto Format options and then
be able to save that change as an option you could select. I am a power user
and would be happy to test this capability if developed.
One other thought. It would be really neat if you could have conditional
formatting within the pivot table. For example show the sales forecast for
the...How to export a query to then xml-format file via VBA?
I would like to transfer my access query to xml-format, but how can I
do it via VBA automatically?
...Publisher and image formats #2
My client is a publisher user.
I have created an illustration for him scanned/prepped in Photoshop.
He wants to receive the image with no background. Can he place an EPS file?
Or a photoshop file with embedded channel or clipping path? Does Publisher
have something like Runarounds?
Thanks for suggestions,
Lost in your world.
carli007 <email@example.com> was very recently heard
> I have created an illustration for him scanned/prepped in Photoshop.
> He wants to receive the image with no background. Can he place an EPS
Yes, and when printed ...