insert DATE automatically #3
I have a worksheet,that has to be updated everyworking day of my company.How
to insert today's date below yesterday's(or last working day) date in date
column,what function will insert today's date automatically after 12 in
midnight by considering the date is working day or not?
You should read the material at top as well, but it is an Event macro and is
installed differently than other macros. Right Click on the workseet tab,
View Code, plop in the code from the above. When you type
something into ...Change Date format
we receive raw data in this date format. i tried using the
format cells--> date --> choose the mm/dd/yy format ..but it does no
work. Could there be something wrong with my excel settings (exce
2000). Thanks for your time.
Apr 10 2004 2:55PM
Message posted from http://www.ExcelForum.com
Excel has stored the values probably as 'Text' after the
import. Try the following:
1. format the cells with the format MMM DD YYYY hh:mm AM
2. Select an empty cell
3. copy this empty cell and select your range with the
4. goto 'Edit - Paste Special...Compare Now() to a European date
This is driving me nuts,
I have a list of certificates. In column B their expiry dates are
entered as Europeans, some at least, do. Like today would be 20080524.
I want these cells to change colour with conditional formating.
For instance becoming yellow when there is less than three months
between now and the expiry date, and then becoming red when there
is less than one month to expiry. Else they should remain without
I have read through a hundred posts dealing with similar needs and
seemingly fine replies, but I get nowhere with my particular sheet.
When I format my B cell as ...Formula for date field
1.I have simple dates in one column (say column A) .
2.In the next column(Column B) I would like the date five months after
Column A to be displayed.Eg if Column A has an entry of 9th June
2007,Column B should display 8th November,2007.
3.A simple formula does not do the job as this does not take into
account the different number of days in different months!
Your post is a bit ambiguous since you don't really say how the
different number of days in months should be handled.
In articl...Date Format turn to Year
I tried to convert the date to YEAR and then the year plus 25 Years
=Year(A1) I'm getting the result 1900 instead of 1965.
I tried to add 25 years later to 1990 from 1965.
Your help would be much apprecated.
What's in A1?
Are you sure it's a real date?
> I tried to convert the date to YEAR and then the year plus 25 Years
> =Year(A1) I'm getting the result 1900 instead of 1965.
> I tried to add 25 years later to 1990 from 1965.
> Your ...Opening and formating a CSV file?
Could someone remind me how to handle CSV files please? I had assumed
the Text Import Wizard would pop-up when I use File|Open, but the data
goes straight into the worksheet. (Presumably because it is not
'delimited'?) Without the Wizard at my side, how do I get each
comma-separated field in its own column?
Terry, West Sussex, UK
Try renaming your .csv file to .txt
Terry Pinnell wrote:
> Could someone remind me how to handle CSV files please? I had assumed
> the Text Import Wizard would pop-up when I use File|Open, but the data
> goes straight into the worksheet...Formatting Cells in Excel 97
Sorry if I sound real stupid but is there anyway that we
can control the column formatting in Excel 97 like let's
say column A = GENERAL(6), column B = GENERAL(4), column C
= TEXT(18) etc?
Appreciate any form of advice, thanks!!
I replied in the programming group. Are you seeking a programming
answer? It's usually best to only post to one group, and include the
remark "Please tell me if I should ask this in another group."
On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <email@example.com> wrote:
>Sorry if I sound real stupid b...using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering
if we can take it 1 step further?
For Activity Dates prior to 2/1/2007 they are using a normal reporting year
and the formulas below take care of Activity dates >2/1/2007?
So for example prior to 2/1/2007
1/1/2006 would have a B_Qtr of 2006-1
1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q")
2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate]))
3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q")
Than...Today's date on an Active X Calendar
Could anyone tell me how to set the properties so that the ActiveX calendar I
have in the database, displays the current date when the program is openend.
I thought this would have been easy, but obviously not!
Thanks for any help.
> Could anyone tell me how to set the properties so that the ActiveX calendar I
> have in the database, displays the current date when the program is openend.
> I thought this would have been easy, but obviously not!
> Thanks for any help.
Jame...Problem access variable in On Format
I'm using Access via Office XP Pro.
I am trying to format the Zip Code on the detail line of my report.
I have tried the following lines of code in both the On Format event and the
On Print event:
If len([PostalCode]) > 5 Then
if len(Me.PostalCode) > 5 Then
In both events and either code, I receive the error message:
Access can't find the field 'PostalCode' referred to in your expression.
If I put "PostalCode" as the source of the report's control...format a CD
Hi, how can I format a cd+rw re writeable disc
...Excel keeps crashing when I try to format any cells?
Every time I try to format a cell in excel the program crashes on me. I've
run a virus check and I can't find any viruses. I seem to have enough space
on my computer. What could be the problem??
Does this occur only on one file, or any? If only on one, does it occur with
that file if it is run on another machine?
Vaya con Dios,
> Every time I try to format a cell in excel the program crashes on me. I've
> run a virus check and I can't find any viruses. I seem to have enough space
> on my computer. What could be ...Getting right date value
I setup my DTPicker control to be used only
as a date control, yet I'm noticing that sometimes
it will give back a date AND a time all in the
same "value" variable.
Since it appears that a variable of type "Date"
can give back both a date and time, how can I
eliminate the time half of a date value???
I might not be able to exactly control the DTPicker
control to give me JUST a date, so I'm just curious
what to do if it gives me back both a date & time.
Try this :
Dim x as date
x = cdate(clng(DTPicker1.value))
&qu...Pivot Table formatting #6
When I format my pivot tables I have "preserve formatting"
checked under "PivotTable Options", and "Autoformat Table"
unchecked. Even so, I either lose portions of my
formatting, or different formatting is applied when I
refresh. Does anyone know what I am doing incorrectly or
have any other suggestions?
Other things to try -- if they don't work, you could record a macro as
you refresh and reformat the pivot table. Then, run that when you want
--Instead of selecting the cells to format the numbers, right-click the
field but...Custom cell formatting
I need to create a custom format for a series of cells that will begin like
I can't figure out what the code character is fora volitile potentially
Can anyone help me??
something like this might work for you but you'll need to put all the
leading digits in for the entry with the alpha character
"MDavison" <firstname.lastname@example.org> wrote in message
> I need to create a custom format for a series ...Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to
the left without automatic moving the odd numbers to the left as well? Can
someone help me?
> I've got a problem with my publication. I can't align even page numbers to
> the left without automatic moving the odd numbers to the left as well? Can
> someone help me?
You need to create a two-page master rather than a one-page master. What
version of Publisher are you running?
Ed Bennett - MVP Microsoft Publisher
...Keyboard shortcut for current date and time
Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me
Ctrl+Shift+; inserts the current time with the date serial as 0 and
not the current date's date serial.
Presently I am adding the two (ie current date and time) to get the
current date and time. Is there a keyboard shortcut that does this?
Thanks in advance.
CTRL+; then SPACE then CTRL+SHIFT+;
Portland, Oregon USA
wrote in message
Ctrl+ ; inserts current dat...how to turn off automatic format in Excel?
Excel automatically change the first character in a cell to be
uppercase. I just want lowercase. How can I turn off this function?
Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence.
"ngoc" <email@example.com> wrote in message
> Excel automatically change the first character in a cell to be
> uppercase. I just want lowercase. How can I turn off this function?
I've always used MS Word as my email editor in Outlook. Is this possible in
Nope. I'm afraid that's only possible using Outlook.
In WLM you have to use the built in editor. Is there anything in particular
that's lacking from the built in editor that you're looking for?
"jrchambe" <firstname.lastname@example.org> wrote in message
> I've always used MS Word as my email editor in Outlook. Is this possible
> Live Mail.
In a Column 'A' sales Commission is calculated and resulta
is as 250, 200, 300, 330.
I wanted to format a cell with a Blinking colours where
value is 100 to 250, 250 to 300, 300 to 350 and 350 &
please help to format my sheet ... thanks
Getting your cells to "blink" is not in the standard Excel formats
What you want would require some extensive programming, certainly, ou
of my capabilities.
However, there are some great things you can do with Excel'
Let's say in column A you have numbers that are the result of formu...Transaction dates a day early
Using M06 with direct connect & bill pay to my bank, the downloaded
transaction dates entered are a day early in Money. If I view my account
through my bank's web interface, the date would show as I'd expect (e.g.
11/21) for a given transaction. But when viewing my account in M06 (again
directly connected/downloaded from bank), that same transaction has a date
of 11/20. All transactions from this bank seem to "post" a day early in
I do let Money change the transaction date from what I entered to what the
bank says (that's the way I want it). I a...input date
is this possible: i will type 011005 and then excel will automatically
format it as 01/10/05 and will be treated as date? thanks.
Only with VBA code or a function in another cell, there is no way with the
user interface of doing this automatically
Microsoft MVP - Excel
"rufino palacol jr" <email@example.com> wrote in message
> hi all,
> is this possible: i will type 011005 and then excel will automatically
> fo...Date Elimination
I have a worksheet with mainly dates in column A in the format of '25
Aug 2008'. Is it possible with a macro or similar to delete lines
beyond a certain date (2 years hence)? Basically, I'm not interested in
data more than 2 years old.
This would eliminate a lot of data and make for a more viewable worksheet.
Do Until ActiveCell.Value =3D ""
dt =3D Date - 730
If ActiveCell.Value <=3D dt Then
End ...Extraneous formatting codes in Index
Strange problem. I'm indexing a 500 page book. There are a couple hundred
index entries. Maybe a half dozen of the entries come through in bold.
There is no visible reason for this. The original entries in the text do not
have a bold code anywhere near them. Suggestions?
Jim Speiser wrote:
> Strange problem. I'm indexing a 500 page book. There are a couple hundred
> index entries. Maybe a half dozen of the entries come through in bold.
> There is no visible reason for this. The original entries in the text do not
> have a bold ...sort special text/numbers in format with many dots
I need your help with sorting in Excel!
I have mani Text fields with numbers into it.
And it should sorted like this
How can I sort this like numbers? My problem is, that not all Numbers have
the same format as x.x.x.x! And I can't change this Text-Fields to Numbers,
because 10.6.1 looks the like 37052 :-(
With your data in column A, insert a blank column at B.
In B1 enter