Preventing Excel Time format
An Excel (2000) spreadsheet that I use regularly has had its column
formats (though fortunately not the data) corrupted following a PC
crash. There's a couple of columns that I use for storing
latitude and longitude data in the format eg 52:00:00.
Since the corruption, Excel insists on treating this column format as
Time (and adding a Date prefix if I try to edit one of the affected
cells). I know I encountered this same problem previously and managed
to fix it but can't remember the fix. If I select the colum and do
Format Cells | Text then all the values immediately change to decima...Can the IF() function loop through cells looking for a certain condition?
Let me first explain what I am trying to accomplish:
I have 2 sheets; one named "Jobs", and the other named "Requests".
Now on the "Jobs" sheet, each row contains a job, and the last column
of each row, I enter an "R", if that job was a request.
Here's an example of the "Jobs" sheet data:
Date Invoice# Customer Name Request?
05/19/04 543218 Larry R
05/19/04 987345 Moe
05/19/04 343529 Fred R
05/19/04 652434 Bubba ...keeping dates in UK format
Is it possible to stop excel changing the date format when it is opene
in another region. i.e from UK to US.
i have created a template that is used by several people in differen
regions, it uses the UK date format for filtering records by date.
However when the template is opened in another region (ie USA) th
dates change format, to the region (locale) set on the users computer.
This stops the template working as intended.
so i want the dates to stay in the UK format no matter where they ar
----------------------------------------------------------------------...Formatting toolbar in Publisher 2000
I can't expand the formatting toolbar in pub2000. I tried
ghost, uninstall, reinstall, win and office updates but no
luck. The icon is just a small grey square. Any ideas?
I'm slow today - what do you mena by expand it? Do you mean it doesn't have
all of the icons you want? Have you tried editing the toolbar?
MVP Microsoft [Publisher]
"Mikep" <firstname.lastname@example.org> wrote in message
>I can't expand the formatting toolbar in pub2000. I tried
> ghost, uninstall, reinstall,...How can I use the format function to change the stored value of a cell in excel?
How can I use the format function to change the stored value of a cel
For example, In a colum I have data that is displayed like this:
But the actual stored data when you go to edit the cell is like this:
12/31/1969 7:59:00 PM
I want the stored data to be like the displayed value so I can sort th
column by the time and not the day.
By the way, I do not know how to do macros or use VB.:
Message posted from http://www.ExcelForum.com
you can search by date and time. No problem with this display. what are
you exactly looking for
Frankf...Possible to change the default cell format?
Hello - I am constantly changing the format of my cells (when they are
the number format) to include the comma separator and to use zero
Is there a way to change the settings so that by default the values
will not have decimal places (and by default they will have the comma
Thanks for any suggestions!
(FYI, I considered creating a simple macro to do this, but I believe
that it would limit me from being able to use the UNDO command as
often as I would like).
To address the "default" number format, you would have to use a template as your
default workbook...Saving a custom format?
I created a custom number format for use on a particular workbook. Now that
custom format is available for use anywhere on that workbook. However, the
custom format is not available in a new workbook or any other existing
workbook. Is there a way to save the custom format so that it is available
in any workbook?
Try putting the format in a blank workbook called Book1 and save this
your XLSTART folder
Jim Orson wrote:
> I created a custom number format for use on a particular work...Formatting Pivot Table Item Row Totals
Is there a way to automatically have the item row totals appear in bold
while the detail remains unbold?
For example, if I have a Pivot Table that displays the following data:
State City Total
Tennessee Nashville 500
Tennessee Total 1,100
I would want the number 1,100 to appear in bold while the other four numbers
(the detail) are not ...SUM of a range conditional on date
I've got 15 columns, A/c No, Description, Jan, Feb, Mar etc and YTD.
I'm wanting to sum the columns conditional to a date, ie sum(C:E)
when the date is 31/03/04.
I've been using if statements but am limited by the maximum of 7 if's
(I need 12).
I'm open to any suggestions,
Message posted from http://www.ExcelForum.com
Maybe you are somewhat more specific. Which columns you want to sum. And how
is the date you mentioned related with data in columns to sum.
A general idea - you can sum every month separately, with some logical
conditions controlling w...Purchase Order Format
I would like to remove the barcode from the purchase orders and the inventory
received report. I just started using RMS about 2 weeks ago and I am stuck.
open the template file in notepad and comment out the barcode section of the
usual file location is c:\Program Files\Microsoft Retail Management
<!-- ========================= -->
<!-- Barcode -->
<!-- ========================= -->
<STYLE> msSExtendedCode93 </STYLE>
<UPCNOT...Can Word Perfect 8 be convertee to Quattro format?
Would like to know if MS Office Pro Excell 2003 can convert Corel Word
Perfect 8 Quattro spreadsheet files to it's format & if so, what is the
Excel cannot read QP files but QP can save a file in XLS format.
Would be happy to fix a few files four you.
Bernard V Liengme
remove caps from email
"Coltt" <Coltt@discussions.microsoft.com> wrote in message
> Would like to know if MS Office Pro Excell 2003 can convert Corel Word
> Perfect 8 Quattro s...Find formatted cells
Is there a way in VBA to scan a range of cells and pick up the references of
any cells that have a particular format applied? The range may have more
than one cell with the format applied and I want to be able to use the
references elsewhere in the workbook.
xl2002 added the ability to find by format.
Before that you could have a macro that would search through your sheets looking
for cells that match a specific format.
But there are lots of things that are in the format of the cell. If you limit
your search criteria (font/boldness/fill color/font color), you may eve...error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19 argumentswith[CT=char]
I am getting following error,
error C2661: 'CStdStr<CT>::Format' : no overloaded function takes 19
If you have the solution please provide it.
Did you compile as Unicode, but still put in an ANSI format string like
..Format("%d") rather than .Format(_T("%d"))
"shilpa" <email@example.com> wrote in message
> I am getting following error,
> error C2661: 'CStdStr<CT>::Format' : no overloaded ...Unusual Date Format
I am writing an Excel 2002 template. Two columns of dates (date from and date
to) are both imported from an external source. The date format is 'cyymmdd'
so today would be '1100421'. The cells are formatted as text.
I need to calculate the difference between the dates shown as a number of
weeks, rounded down to the nearest whole number.
Any help would be appreciated.
"Grey Old Man" <GreyOldMan@discussions.microsoft.com&g...Formatting cells #5
I have a spreadsheet in which some cells are placing a decimal point in the numbers we enter. I think the cell thinks we are entering dollar amounts. I right click on the cell and choose format cell and on the Number Tab the Category Field is marked General and to the right it says "General format cells have no specifc number format". However in the sample box is shows 283802.07, it is placing a decimal point anyway. Any ideas on how to fix this? I just need to enter the numbers without a decimal point
Tools/Options/Edit uncheck the Fixed decimal places checkbo...Importing from Excel with HTML Formatting
A client of mine has been on a web based system for email and CRM.
They now what to use Outlook and a proprietary CRM system. The online
system has sent us a SQL file with all the emails and I can put those
out so we can put them in Outlook, the problem is that the body of
each message is in HTML and when we import the body of the message is
plain text i.e. <html>text</html> which makes is super hard to read.
Query: Does anyone know how to import text based messages into Outlook
and have the message be in HTML format?
...Formatting a Date field into a form?????
I just know this is going to be easy.
using the code below to place 2 dates into specific cells on a sheet
the generates my autofilter to run a query between these dates, this
bit works fine, apart from the date entry, i would like the date to
show as "dd mm yy" but when i enter it into the form it appears as "mm
dd yy" i know that it will be a case of adding the 'Format' to the
code within the form but am having trouble exactly where to place it.
Any help please
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Da...FORMAT CELLS #10
I have a worksheet. Within that worksheet I have a few
cells which if I right click on gives me the menu options
and when I select the format cells nothing happens. While
it opens a dialog box for other cells.
I wanted to protect some cells by selecting the format
cells options. Can someone tell me what is happening and
why nothing happens if I select the format cell option ?
The only time I've seen this is when the original workbook was created in a
different program--crystal reports or Lotus 123.
I included a few "sacrificial" cells in my selection, made my...Conditional Formatting 05-21-10
I have an input model which I have populated with data in each cell by
entering =SUM(Balance_Sheet_Retrieve!A1), and so on. Some of the cells will
be locked so they cannot be changed and these are coloured light green. Input
cells - which can be changed are coloured light yellow.
What I would like to do is use conditional formatting to change the
background colour of any input cell which no longer uses the exact original
formula - so, for example:
if the cell just has a value OR has a different formula (even if it is the
original formula plus a new one, or plus/minus a...date in text format #2
I'm trying to do a mail merge in Publisher than has a date field. It converts
it to text - and becomes a 5 digit number.Publisher is very limited in terms
of mail merge power (unlike Word - but I can't use Word due to Text boxes and
needing to get several on a page) . I then decided I'd convert the date field
in excel into Text, so then Publisher would then read it in the correct
format. However, I can't get away from the 5 digit number. I've read
everything I can find about date and text. I"ve tried copy and "paste as
values", etc - and I get the 5 di...Custom Formula Requires Custom Formatting
As suggested from a previous post I am using the following formula to fill a
="(AO) "&'Source Doc'!B49&REPT(" ",49-LEN('Source Doc'!B49&'Source
I have a need to make the (AO) Bold & Italiacs, then the data from 'Source
Doc'!B49 should be bold and dark blue. Leaving the data from C49 as
Cells that contain formulas can't have this kind of character by character
> As suggested from a previous post I am using the...get rid of time format w/msec
i have data column with the following format- 04:01:59:22
which means hh:mm:ss:msms
I want to get rid of msec. They dont allow to import this csv to access db.
How to convert hh:mm:ss:msms to hh:mm:ss ????????????????????
Highlight the data column with the time format. Go to Format, Cell, Number,
Custom, and select h:mm:ss. Go up to Type (just above your selection) and
change the h to hh.
> i have data column with the following format- 04:01:59:22
> which means hh:mm:ss:msms
> I want to get rid of msec. They dont allow...Multiple Conditional Formatting-HELP
I need the formula for--If K1 or L1 is less than 100, or if E1 is greater
than 60, shade cell A1 to dark gray. Then I would need to carry it down the
length of A column. Using Excel 2007. Thank you.
Select all of the target cells and set the CF using a formula of
"Cindy" <Cindy@discussions.microsoft.com> wrote in message
>I need the formula for--If K1 or L1 is less than 100, or if E1 is greater
> than 60, shade cell A1 to dark gray. Then I would need...Problem With Text & number format
I hv this sample data in excel
1) How to delete spacing, dashes, character etc leaving the number only..in
my above sample the clean data that i want to get is 0191234567
2) I tried to use replace function (to delete dash,space etc) but the
leading 0 will not be displayed.
What will be the best approach?
There are several options for displaying leading zeros depending on the way
you want to use the strings.
1. Custom formatting the cells with format code "0000000000" (10 zero digits).
2....Negative Cell Format
I would like to know if a cell can be fomatted negative
so that any subsequent entry appears as a negative number
with out having to enter the minus sign.
If you only want it to "appear" to be negative:
Note that positive values displayed with the leading minus sign will not
be treated as negative in calculations.
If you want to have the values automatically be negative, try this event
macro (right-click the worksheet tab and choose View Code):
Private Sub Worksheet_Change(ByVal Target As Excel.Range)