I have been working in a worksheet with 10 tabs, and suddenly all of my numbers turned to long date format. I manually changed them all back to what they were. General, Currency, %, etc. but the default seems to be stuck in the long date format. When I create a new tab it is automatically all long dates. Very strange.
Oh, and the other weird thing is when I send it to other people, some people see the correct formats and when other's open it the numbers all go back to the long date format. VERY Frustrating.
Would love to get some advice.

I am looking for a formula for a short number format. For example, $2,180802.35 ---> $2180K. Any ideas?

thanks to all!
C

Hi folks,

Well I had a lovely data entry form working with DTpicker - and now I find despite thinking all installations of excel were the same across work (and at my new home station) they're apparerently not. DTpicker is not on my machine or on the machine primarily intended for use.

So I've decided I can't use controls at all - and have been trying to do a textbox based date. To save time, and give some hints, I want the date to be generated from todays date minus five years (give or take a few days) to get the student's approximate birthyear.

I've tried a number of code variations based on

'Textday = Format(Date, "dd/mm/yyy")

but they just keep giving errors.

My code as it stands is:
Code:

Private Sub CmbYear_Change()
Dim TextDate As String
TextDate = (CDate(Now))
    Dim YearsOff As Variant
YearsOff = CmbYear.Value + 5
txtDate.Value = TextDate - (YearsOff * 365)
End Sub

This works fine - I change the dropdown and it takes the years off correctly and in the uk format, BUT it gives me a time as well and I can't fix it.

Suggestions?

Hi

I don't tend to post to forums since the oracle (Google) tends to answer most of my questions, but this one I have been wondering about for some years.
I love shortcut keys and hate the mouse. I therefore managed to configure my Excel such that I can do pretty much anything with the keyboard only and faster than using the mouse.

We use the date format yyyy/MM/dd for uploading data to a database. In my Regional Settings in Windows 7 (same with XP and Vista before) this is set up as the "Short Date" which is consistent with my country. This works fairly well. When I Press CTRL-1, TAB (once) to "Date" and hit ENTER, I get my date formatted in the correct format.

However, when I hit CTRL-SHIFT-3, the date format is dd-MMM-yy. I have checked my Regional Settings, as most posts suggest, but no luck. This format is not listed there at all. Where does Excel get this format? Is it hard coded? How can I configure Excel to use the short date format configured on my OS, or for that matter just yyyy/MM/dd?

I have in recent times found a workaround in the form of a Macro configured on my Alt-9 key:
Code:

Sub DateFormat()
    Selection.NumberFormat = "yyyy/mm/dd;@"
End Sub

I prefer to find a native solution because this will: Help me understand why this is not working Sooth my OCD a bit Release one of my precious Alt-# keys Allow me to set it up easier on new machines
Thanks in advance

Model_Citizen

Hi,

I have a chart that on the X axis I have dates. I want the date to show 1/6 instead of 1/6/2008. I changed the source data format to say 1/6 and I changed the pivot table to show 1/6 but the chart still says 1/6/2008.

Is there anyway to change this?

Thank you,
- Lior

Hi,

Im linked to a database through ODBC connection. When I pull the data in it give me this format "5/29/2008 12:00:05 AM", I am trying to change the format to " 5/29/2008" do I can group by date and have all the info fot that date round up.

I tried using DateA: Format([Date],"m/d/yyyy"), but it does not sort correctly and it feels like its not the correct way.

Please let me know how I can fix this.

Thank you,
- Lior

Hi,

Im currently using With / End With statements to apply formulas or link in data via a named range.

What I want to be able to do is change the property of what I'm linking in to be in date format.

For example I have the code
Set FiscalYrPrd = Range("AG7:AH" & .Range("AA" & .Rows.Count).End(xlUp).Row)
With FiscalYrPrd
.Formula = "=C7"
.Value = .Value
End With

my understanding if that your changing the property of the variable to be a formula and then a value, therefore is it possible to change the format property of the range so its in date format (dd/mm/yy)?

Any help would be greatly appreciated!
In addition - same request but in numerical format with zero d.p?

Many thanks in advance.

Rgd,

SPiercy

This should be a super-simple formatting question but I can't get it to work.
The columns are very small and I'd like to put in the date or a number and have it span into Column B from Column A.
This works easily with text. But it won't work for me with any number or a date.
I've looked though all the formatting options and can't figure it out.

My first sheet contains the follwing cols.

DATE SL.# NAME AMOUNT NO. OF PYMNTS MADE 01/05/2005 1 ADAN 300
12/06/2006 2 BAND 200
14/07/2007 3 CHARGE 500

and my second sheet contains the date and voucher # of the monthly payments made.

SL.# NAME AMOUNT May-05 Jun-05 Jul-05 Aug-05 Sep-05 Oct-05 Nov-05


DATE VOUCHER# DATE VOUCHER# DATE VOUCHER# DATE VOUCHER# DATE VOUCHER# DATE VOUCHER# DATE VOUCHER# 1 ADAN 300 01/05/2008 12 02/06/2005 9 05/07/2005 4 05/08/2005 12 05/09/2005 2 06/10/2005 5 06/11/2005 1 2 BANH 200













3 CHARGE 500













4 DARLING 400














From the 2nd sheet I need to count the number of payments made by each person for which I only have the date and Vr# in the above format.

Could you help me in this please?

Thanks.

Sheet 1 contains the following columns :
Date Sl# Name Amount No. of pymnts
15-8-09 1 Adam 300.00

Sheet 2 contains the following cols.
Sl# Name Amount Month 1 Month 2 .....Month 100
Date Vr.# Date Vr. # Date Vr.#......
1 Adam 300.00 15-8-09 5 10-10-09 ....

I want to count the number of payments made by 1 Adams.

How do I go about this?