Date Format #17
I would like to convert a date that I've input from another program. When I
input it I get 'Aug-28-2008. I want it to just be a regular date - so it
would be *8/28/2008 if I were to select it in Format Cells.
It says that I have General as the Category selected. When I switch it to
Custom - nothing seems to happen.
First create a small table in the worksheet:
and assign the name:
to the table. Then, with your data in A1, try:
=DATE(RIGHT(A1,4),VLOOKUP(LEFT(A1,3),mnths,2,FALSE...Year formatting in Excel '03
I have an interesting situation where a client is interested in only
entering the year in the field but the a date format. The
unfortunate year result is 1905. Even with a custom value such as
yyyy Excel still returns the 1905 "year". Now after some reading on
the google, I have found that the reason why this happens but not how
to make it work.
Format the cell as General, not yyyy
(there's no email, no snail ...Format pasting using keyboard shortcuts
I am working on a Spreadsheet project where I have to use the keyboard to
Copy & Paste a Format from one cell to another.
Does anyone know how this can be done.
In Word to Copy & Paste a Format it is CTRL Shift C, CTRL Shift V which does
not appear to be the case in Excel.
not as easy as Word but
Control & C to copy
Alt & E then S then T and enter
to paste format
(personally, i would use the format painter icon on the standard toolbar)
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
&quo...Date Formatting problem #2
I have a excel (professional xp). I have a problem
formatting a text field into a date field. When I format
the field nothing happens, until I double click on the
field and then it formats. The problem is that I have
over 8000 lines and I can't do this for every field.
Does anyone have any ideas?
Enter 1 in a blank cell and copy it. Highlight the 'dates' and Edit>Paste
special...>Values+Multiply. With them still highlighted, reformat them as
Date (Format>Cells...Number.Date). Don't worry that they all look like
numbers before formatting....E-Newsletter #2
I am trying to send an e-newsletter embedded into the message. When I preview
the publication in the browser it looks perfect although when I try to send
the document it appears in the same format as when I am working on it- i.e
not in HTML. Help!
...how to get the format of this cell right?
When I let go:
Selection.Replace " ", Replacement:="-", Lookat:=xlPart, _
MatchCase:=False, SearchFormat:=False, _
" " is entered by means of an array:
Words = Array(" ", "+", "/", "---", "--", "&")
on a cell containing: 5 a
I would expect to get: 5-a as a result.
Unfortunately, it produces 5:00:00-AM. How can I solve this tiny but
Pet...Disable Formatting marks when using MS Word 2003 in Outlook
I'm using Outlook 2003 with Word 2003 as my editor. While
cutting and pasting a web page into a new msg Outlook
started displaying the Formating marks like the Paragraph
symbol, spacing, etc. This occurs for both new messages
and replies. I want to turn displaying these marks off
while using Word 2003 as my editor but have been unable
to do so. They remain unless I remove Word as my Outlook
Thanks in advance for any help
Load up Word, click on Tools, then Options.
In the View tab, there is a section called Formatting marks. You probalby
have the "All" ...Formatting responses
When I send out a note or forward onr, it goes in HTML format, showing all
my grapics, but when I respond to a note, it converts back to basic text.
Is there anything I can do to allow responses to automatically go out in
HTML (Or is this a function of how the sender has formatted his message to
By default, replies (and forwards too) are formatted the same as the original
message -- you'd have to change it manually.
MVP - Outlook
*** Messages sent to my e-mail address will NOT be answered -- please reply
only to the newsgroup t...How do I format parenthesis around negative numbers.
Seems to me Office XP could do this...How to in Office 2003 ?
this setting depends on your language settings. If Excel does not offer
this as default format you can just enter this format as custom format
> Seems to me Office XP could do this...How to in Office 2003 ?
There's a windows setting you can change:
The way I'd get to in Win98 is:
Windows Start button|settings|control panel|Regional Settings
Currency Tab|Negative Number Format
> Seems to me Office XP could do this...How to ...CSV format?
I have been forewarned that I will be working on creating an Excel
spreadsheet in a CSV format. Can someone explain to me what that it? I am
having a problem finding it in Excel's Help.
Thanks a bunch!
CSV stands for Comma Separated Values. This means that when the file is
saved, all formatting and formulas will be lost - it will just be plain
text. The file can be opened with a text editor, Excel, Access and numerous
Hope this helps.
"Teri" <Teri@discussions.microsoft.com> wrote in message
news:A2729370-33E3-4F52-BEF2-F9358B...how do i disable warning when saving file in csv format?
I would like to not have a message pop up every time i save my file in csv
format, warning me about the fact that csv doesn't support certain things.
how can i turn off that feature forever?
...Excel to PDG Format Error
I am used to using Excel 2007 to convert my workbooks into PDF's. However,
recently, all of a sudden, everytime I PDF a workbook my Formating and graphs
are all wrong. I have noticed that this only happens when I do multiple
sheets, but the problem with doing individual ones is that I can't combine
them with reader.
Try Dr. Dan's All-Purpose Excel-PDF Cure:
Select all worksheets by <ctrl> or <shift> clicking
from the menu bar: File > Page Setup
click into the Page Quality box, and select 600dpi, if it already says
600dpi, select it again.
...Formatting from VBE
Hello all !
I need to format a cell from VBE...
it must formattes as number with 2 decimals..
then i will insert in my work..
help me please !!
You can set a cell's number format in VBA:
Range("A1").Numberformat = "0.00"
Hello all !
> I need to format a cell from VBE...
> it must formattes as number with 2 decimals..
> then i will insert in my work..
> help me please !!
Regard...cell formatting #4
I am trying to format a column so that it keeps track of computer
hardware mac addresses.
-Each cell must be limited to 17 characters only.
-Cells must have 5 visible permanent colons so that users may only
enter the remaining 12 letters/numbers.
-cells must be restricted to entry of letters and numbers (no special
-users must be presented with an error message if they try to leave the
cell without entering 12 letters/numbers.
-column must not accept duplicate mac addresses.
Can anyone give me some direction on how to do this?
-----------------------------------------------...Counting Cells Based on Format
Does anyone know if there is a formula that would allow me to count th
number of times a specific cell format (Text Color) occurs in
Message posted from http://www.ExcelForum.com
you'll need VBA for this. Below a repost from today
using a procedure from Bob Phillips and Harlan Grove you may try the
to count all red cells (background color) within the range A1:A100
to count all red cells (font color) within the range A1:A100
To get the c...Formatting Cells #10
I have a worksheet full of numbers 7 columns and 500 rows. Column number 4 has data (12344, 128763, A8923, 1284D, etc...) and I want to convert it into ('12344', '128763', 'A8923', '1284D', etc...)
Basically, I just want to add single quotes around the text field in each cell
NE help will be appreciated
one way: use the following macro
dim rng as range
dim cell as range
set rng = activesheet.range("D1:D100")
for each cell in rng
if cell.value<>"" then
cell.value="'" & cell.val...Re-formatting of dates from Excel to CSV
When I save an Excel worksheet as a CSV file the dates are re-formatted from
04/11/2004 to 4/11/2004 (British style Date ddmmyyyy). This doesnt happen
immediately on saving but when the CSV file is closed and then reopened.
Any suggestions how I can prevent it dropping the leading zero?
In a word, No!! All I can suggest is you reformat the cells when the file
is opened. I tried preceeding the date with a ' and Excel got shot of the '
AND the leading 0. The only other suggestion I have is to save as a TXT
file and use the text import wizard to format the ...Conditional formatting wont work?
I have a Text box on my form [Age] but when I ask it to Conditional Format
it wont recognize "2yo" this is the query field to the text box
Age: funCalcAge(Format('01-Aug-' &
Any Help Thanks..........Bob
...Converting numbers to date format from csv files
I have data submitted as csv files which i have saved as excel spreadsheets.
Problem is the dates in the csv file are 20020105 etc, I need to convert
these to date format in order for me to calculate between sets of dates. All
I get is ###### (it's not a case of the column being too short). Tearing my
Select the column and use Data / Text to Columns. One of the dialog box
steps allows you to tell Excel that the value is a date - and how it is laid
out. I think it's the third box in and it says something like Column Data
Format in the top right corner.
&q...Format problem on exporting a query to txt
Exporting a query as a text file and I choose CSV and " as text separator.
I need " to wrap the numbers but I cant figure out how to do that.
What I get is
What I need is
Anyone know how to achieve this.
Use CStr function to cast the numbers as string values:
SELECT CStr([TAG]) AS tTAG, CStr([Qty]) AS tQty
<MS ACCESS MVP>
"Pat Coleman" <pcoleman@foodirela...Problem with V-Basic code for displaying times in hh:mm format
I am NOT a Visual Basic expert by any stretch. A while ago, I asked for some
help re setting up the code so that someone could quickly enter just the
numbers for a time, and the program would change that to the correct hh:mm
format when you tab out of the cell.
I received the following suggestion:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 3 Or Target.Column > 15 Then Exit Sub
If Target.Column > 6 And Target.Column < 12 Then Exit Sub
If Target.Count > 1 Then Exit Sub
tlen = Len(Target.Val...Formatting Dates from 1700 & 1800s
Running Office 2007. How can I get (or format) dates from the 1700s through
2010 to sort? It sorts dates ascendingly from the 1900 and 2000s just fine
but throws the 1700/1800 dates at the bottom and then not in any particular
I've already tried formatting dates as almost every choice in the date
format window and I've tried typing dates as 1/23/1899, January 23, 1899,
etc. but nothing sorts correctly (ascending or descending).
Thanks for any help you can give me.
Excel doesn't support those old dates.
It only goes back to Jan 1, 1900.
You could e...Cell formatting #2
Can I format a cell to change numerical data from one cell
to text data; i.e. A1=1 A2=one
No built-in Format or Function, but you can build your own User Defined
Function using the info at:
OR download Laurent Longre's MOREFUNC.XLL from here:
Has a Numbers to Words Function along with many other useful Functions.
Gord Dibben Excel MVP - XL97 SR2 & XL2002
On Wed, 30 Jul 2003 11:52:55 -0700, "Mark Strohmeyer"
&...The source data is not in the required format
I'm trying to import a list of 10 contacts. They were all unsuccessful. The
error message "The source data is not in the required format" appears. The
error stop at the company column which is a lookup to another table and it is
a required field. I've tried replacing the company name with both plain text
I'm also trying to import a field name Contact Steward. This field contains
a CRM User login ID and a field name Language which is a pick list item
containing the words English or French.
What should I try for the company field?
What shou...Creating a Formula to Format Column automatically? #3
Can I record it as a macro or anything like that? I have to run it o
five sheets. I've tried to conditional formatting, and it works only o
one cell, unless I'm doing it wrong
bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480
View this thread: http://www.excelforum.com/showthread.php?threadid=26434
not sure what you are trying to do but conditional
formating can be coped and pasted. you have also
conditional format an entire column/row by selected the