Help needed changing date formats from m/d/y to d/m/y
I have a spreadsheet with a column of dates in the format ddd
m/d/yyyy, e.g. "Mon 9/29/2003". The data has come in as text as I have
cut and pasted it from another source.
I sort of have something working, but it only works for dates with 2
digits (i.e. from October (10th month) on and from the 10th of each
month on), e.g. "Wed 29/10/2003". The formula I have is:
Can anyone help me get this to work for all dates?
Thanks in advance.
Select your column.
Choose Data/Text To Columns. Select the Delimited ...SQL Statement to Update ReorderPoint based on QTY Sold During time
I am using RMS 1.3 with SQL 2005
SQL Statement to Update ReorderPoint based on QTY Sold During certin time
2weeks, 3weeks, Month, 2 Month
We sell unique products where reorder level changes every 2-3 months. We
want to run a SQL query and update item table with ReorderPoint and
ReorderQTY based on QTY sold during specific period.
Please review this SQL Statement
SUM(lastweek.Quantity) as LastWeek,
SUM(lastqy.Quantity) as LastQY
LEFT JOIN TransactionEntry l...Time Sheet
I have a time sheet with four fields:
Time In (C2), Lunch Out (D2), Lunch In (E2), Time Out (F2)
I'm trying to create a formula in cell G2 to calculate the regular hours and
another formula in H2 for the overtime hours, but have been unsuccessful.
Current fields are formatted as Number | Custom | h:mm AM/PM for C2 thru F2
and are formatted as Number | Custom | h:mm for G2 and H2.
Can anyone help me.
Look into CPearson Web it has all the working with overtime Hs
> I have a time sheet wi...Format Column
I have a column with inmate id numbers in it. I imported them from a
text based program. I made a custom formatting for the column because
all the id numbers begin with zero. IE 00112356 or 01555666 etc...
The format I used was 00000000 under custom.
The problem now is that I want to import them into access but access
doesn't see the zero at the beginning. How can I make it show the
literal number including the zeros? I tried adding the '01222555
before the number, but I would have to manually append 2000 records.
Is there a faster way?
you could use a helper column...Hide formatting marks
Would anyone be able to advise how I get the formatting marks to be hidden in my return e-mails that I reply to? Please advise
when you reply to your mail, goto tools-->options-->view and under
formatting marks, deselect whichever you dont want to see.
This posting is provided "AS IS" with no warranties, and confers no rights.
...Excel automatically changes the formatting of the cell to "Time"
I have a long column of numbers [dates in the YY:DD format]. I wanted to
":61" to ":59". Even though the cells are initially formatted as Text, as
soon as I make the change, Excel changes the formatting to Time, and the cell
with the change now has text ":59:00" in it.
Is it possible to force Excel to keep the cells formatted as Text?
If the cells are truly formatted as text (Format - Cells - Number - Text),
the formatting should never change, and you should always see exactly what
you've typed. Give us an example...Cell will not format
Right click on cells that were copied over from another excel sheet and can
not format the cell - right click - format does not work
Check whether the Worksheet is protected.
If this post helps, Click Yes!
> Right click on cells that were copied over from another excel sheet and can
> not format the cell - right click - format does not work
Right-click>format cells does not show up or you cannot re-format the data?
What type of data are in the cells?
Maybe dates ...Formatting of email returned.
Hi there, I sent the email below in HTML format using outlook 2007.
This email bounced and was sent back to me.
I received it in the format below. Why does it appear in this format,
where the formatting is all messed up.
What are all these characters below. =20. Also look at the subject
Why does it appear like this? What is the solution?
---------------------------------------------------------------------------------------------------------------...NAICS Code Cell Format
I recently downloaded some US Census data (NAICS codes) into Excel and
they have a cell format that I am unable to change. When I sort a list
of numbers (e.g. 10, 12, 101, 111, 112), rather than sorting these
numbers from lowest to highest (or vice versa), the numbers are sorted
The list is being sorted as if the numbers have a hidden decimal after
the first two numbers. I have tried to altering the number format to
no avail. Does anyone have a suggestion for how I can change the cell
format so that the number...why no video format for my windows media creating dvd
my creating dvd has only data format; have seen some references to video
format as well. this prevents me from copying movies, tv shows to a dvd that
can then be shown on another dvd player
"webbnc" <email@example.com> wrote in message
> my creating dvd has only data format; have seen some references to video
> format as well. this prevents me from copying movies, tv shows to a dvd
> can then be shown on another dvd player
Your question may be better answered in a news...csv format is wrong in Denmark
When I make csv file from excel 2007, then I do not get a comma-separated
file. The cells are separated with a semicolon and the the normal dot in
numbers is a comma. This is the case when you live in Denmark.
The problem with the "Danish" excel csv format is that we can not export it
to what ever we want. If I want to use it in Microsoft Visual Studio Team
System, for a unit test, then it does understand the format.
Is it possible in Office to save in correct cvs format?
This is something to be done with the Windows setting. Start>Settings>Control
Panel>...If Statement with Time
I am trying to write IF THEN Statement using time numbers (i.e. 16:00, 13:30,
6:30). It doesn't appear to be working. Here is a sample
=IF(AND(E4<17,E4>4),"NP", "P") where E4 is a cell that contains the
military time. I keep only getting a value of P returned even when it should
be NP. I tried using 17:00 and 4:00 in the statement and got an error
I'm trying to create a "time-line" chart on both sides of the X axis.
For example, item '1' lasts (3) seconds long and is 'positive' (above X
axis). Item '2' lasts (2) seconds and is 'negative' (below X axis). Item
'3' lasts (5) seconds and is 'positive'. Every time I graph this the lengths
of the items are graphed vertically instead of horizontally. Any suggestions?
Try using a bar chart as the chart type. (Note: If you
don't want your timeline...Time Required to Save a File
I have a large Excel file that takes about 15 seconds to save if I have two
other files open that the first file is linked to. If I do not have these
two other files open it takes several minutes to save the first file. Does
anyone know what is causing this, and if it can be fixed? I do not always
remember to open the other two files so I am frequently stuck waiting for the
first file to save.
Do you have calculation set to manual?
There's an option to recalculate before you save.
If you have manual calculation set, you could turn off ...Custom number format button
I'd like to create a toolbar button linked to a custom number format so that
clicking on the button would re-format the active cell or range to my custom
Alt-F11 to enter the VBE
Insert | Module
Paste the following code:
Public Sub aTester()
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Alt=F11 to return to Excel
View | Toolbars | Customize | Select 'Commands' tab
Select 'Macros' in the left-hand Categories window
Drag the smiley icon ftom the commands window to your toolbar
Ri...Using formatted cells in formulas
I have been provided a worksheet with a list of 5-digit part numbers that
the creator of the worksheet formatted as zip code to preserve leading
zeros. I need to use these cells in a formula which uses the "&" operator to
tack on a suffix. For example:
Original part number cell A1 = 2345 (with zip code formatting displays as
I need cell A2 to be 02345XX, so I'm using the following formula: A1&"XX".
Problem is, when I do this, Excel drops the leading zero and displays
2345XX. Is there a way to have Excel use the formatted display string for
cell A1 inst...Why do I get this ###### instead of 11:00 p.m. in formatted cell?
One reason could be that the column isn't wide enough. Try widening the
Microsoft Excel MVP
Always supply your formula and the values of the input cells
Microsoft MVP - Excel
"new user" <new firstname.lastname@example.org> wrote in message news:32404C0C-FB66-4D90-A301-42D0322E2CFE@microsoft.com...
On 15 =F1=E5=EF, 19:43, new user <new u...@discussions.microsoft.com> wrote=
Not to forget: if cell contains fomula that evaluates time value and
result turns to be negative, it will be shown as ###### no...cell formatting #7
I have sheet that is not letting me format a number unless I doubl
click on the cell.
Say the cell value is 5.00000 I cannot reduce the number of decimals.
There is over 12k rows of data so I don't particularly want to g
through each cell.
Thanks in advanc
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
Try - tools / options / edit / fixed decimal.
>I have sheet that is not letting me format a number
unless I double
>clic...Sending HTML format from Exchange 2007
I am looking for a way to force all messages sent from exchange to go out in
an HTML format.
We are adding some branding info to outgoing email and it needs to be in
HTML format. There are several clients using exchange, so the only logical
place to manage things is on the server. Looks like any way to do this may
be well hidden.
I believe that can only be done on the email client.
"Manuel" <Manuel@discussions.microsoft.com> wrote in message
>I am looking...Chart-date axis format
I am using date-times as the x axis. The differences between two point is not
always the same. Could be 3 days, 8 days etc. But the data axis formats are
in day order day 1, 2, 3, 4 etc.. I can't seem to modify teh axis format
correctly to get what I need. What format option can I set so that if I have
3 data points on day 1, 5 and 15 that I have 3 dates on the x axis instead of
the 15 I get by default.
Thanks in advance for any hints/help
Right click the chart; select Chart Options
On the Axes tab; chance the Primary axis from Automatic (or Time Series) to
--...Help with Time Input
I am working on building a spreadsheet that would enable the user to easily
select the specific time they are looking for using the format MM/DD/YYYY
Does anyone have any examples or suggestions on automating this task, such
as a macro or drop down menus for easily selecting the input?
Huh? Surely you don't mean a drop down for every second of every
day from Jan 1st 1900 to eternity.
I don't know what you are really looking for but it would be faster to
just type in the date and time than to go through dropdowns for
each of year/mo...hours from job-sheet to time-sheet
We have a sheet titled Job-Sheet. In column A1-A20 there's days of a week.
In column B1-B20 there's names. In column C1-C20 there are hours.
MON Anderson, Josh 8
MON Bailey, John 8
MON Blakely, Brett 8
TUE Eddings, Cindy 8
TUE Floyd, Bill 8
TUE Anderson, Josh 8
WED Bailey, John 9
WED Blakely, Brett 9
WED Eddings, Cindy 9
THUR Anderson, Josh 10
THUR Bailey, John 10
FRI Anderson, Josh 10
SAT Anderson, Josh 9
SAT ...Run time error 3077
I am getting a run time error 3077 whenever I select a record from the
combobox with an apostophe as part of the text in the combo box. Can anyone
tell me what the apostrophes do in the code below and if there is a good
error handling feature to prevent this so I can have company names with
apostrphes in the combo box without getting the run time error? Listed below
is the code:
Private Sub CboCompanyLookup_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Client Name] = '...Windows Installer loads every time I open Excel 2007
I'm facing the following problem:
when I open Excel 2007 the windows installer loads every time... is
not going to give me any error, but it's something annoying... does
anyone has any solution???
p.s. this happens also with other office 2007 applications
Try clicking "Help" and then "Detect and Repair..." That worked for
me when I had the same problem.
...Simple Macro to copy each row in the database "X" times? (Excel 2002)
This is a multi-part message in MIME format.
I was wondering if someone could help me with a macro to cut down on =
some repetitive task activity.
I have a regularly increasing DB of address information in Excel with =
several hundred entries with row 1 as the header row. Depending on the =
demand, we have to create mail labels in various quantities per address, =
consecutively. I foresee a macro that when kicked off will prompt the =
user to ente...