Computer Support Forum

(Solved) Excel Date format

Question: (Solved) Excel Date format

I have an elaborate Excel file (a weight reduction program) that includes a cell that shows the first day of a month (dd-mm-yy). Based upon which month of the year is in that cell, I want to make an IF statement that uses the number of days in that month (28,30 or 31 days). If I copy that cell and format it as a DATE per the mm format, it just prints the first letter of that month. Therefore, if there was a "J" in that second cell, the IF statement would not know if it was Jan (31 days), June (30 days) or July (31 days).

Anyone have any ideas?

Thanks
[email protected]

Relevance 100%
Preferred Solution: (Solved) Excel Date format

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: (Solved) Excel Date format

Hiya

Moved you to Business Applications, as you may get more help here

eddie

3 more replies
Relevance 86.92%

I am setting up a spreadsheet for the year and want to use it year over year. I have the cell set up with the date (1/1/2011) with a format of d so that it displays the date as Sat, 1/2/2011 - Sun, etc. Since I want to use this year after year with minimal work and I want to highlight the cells that are equal to Sat and Sun, how would I set up that formula. I tried the basic =A1="Sat" and without quotes but it just returns #NAME. I know working with dates in formulas is different but can't figure out what I need to use.
 

Answer:Solved: Excel 2007: conditional format when date displayed as format d

8 more replies
Relevance 85.69%

Since installing Windows 10 - previously on Windows 7 - all dates in Excel have turned into the American date format i.e. previously 12/08/15 in a cell displayed as 12th August 2015. Now it displays correctly but the cell contents have changed, so, in previous example, the cell now contains 08/12/15. How can I put this right? I have gone into anything I could see re language and have ensured it is English (United Kingdom) but this hasn't made any difference.

Answer:Windows 10 has turned my British date format in Excel into the American date format. How can I get it to turn back?

Freeeeeeeeeeeeedom!

10 more replies
Relevance 82.41%

Great!! I solve the problem also from DerbyDad03, smart you.After input date 29-Aug-17, it automatically changed into 0-Jan-00, the formula box shown =8/29/2017, but it won't affect the sort function. If try to priced with " ' " instead of "=" , it shown 8/29/2017. Follow your suggestion, "Turning off the Transition option" then, fixed.

More replies
Relevance 81.59%

I am trying to enter a date in a column as yymmdd but when I enter 09 as the year is drops the 0. I tried using different formats but that is not working either. For example, I changed the cell format to yymmdd and when I entered 090120 it displays 460926. Can someone tell me what I need to do to get it to display as I need?
Thanks!!
 

Answer:Solved: Excel 2002 Date Format

6 more replies
Relevance 81.59%

I have a user who is trying to update an Excel spreadsheet somebody sent them. In one cell, there is a reference to the date located in another cell. However, the way it is formatted, the date in the cell displays as just a number instead of the date ("September 22, 2010" displays in the 2nd cell as "40443".

In the second cell, there is some text along with the formula referencing the original cell containing the date. In the formula bar, it looks like this: ...Los Angeles, California 90013, on "&AP29&"

Yet in the spreadsheet, it displays like this: ...Los Angeles, California 90013, on 40443

In the original/refenced cell (AP29), the text entered into the cell is 9/22/2010, and is formatted to display as September 22, 2010.

Any idea how to correct the formula to display the date correctly?

Thanks a bunch.
 

Answer:Solved: Date format - Excel 2007

Hi Traci.

It's because the underlying value of the date is a number, which is how Excel interprets it no matter how it's formatted. To change it to display the date, you have to tell it it's a date within the formula. Try this:

...Los Angeles, California 90013, on "&TEXT(AP29,"m/dd/yy")&"

Without seeing the whole formula in the second cell I'm not quite sure about the positioning of the ampersands and quotes, but play around with it a little. Let me know if it doesn't work.
 

3 more replies
Relevance 81.59%

Down Under we show our dates as dd/mm/yy. I have a user who types in 3/4/00 and the system automatically reverses the date to 4/3/00. I have checked the properties on the regional settings, and the date options in Excel are also correct. I have tried, in Tools/Options/Calculations to change the date format to 1904, but the system wont accept it.

Any ideas for resolution appreciated.

Ms. Kumara
 

Answer:(Solved) Excel date format wrong

In my 4 years in customer services for a well known computer company, I have never seen that before.

Microsoft Excel does not control the dates nor the settings, ie currency etc. Which version are you using?

Try using another Microsoft program like Word to see if you have the same problem, if not, then you would need to speak to Microsoft in New Zealand.

Also, double check to see under Regional Settings in Control Panel, in regional settings tab, make sure the setting is English (New Zealand), and press apply (if it is not greyed out. If it asks you to restart, press no and go to the Currency and Date tabs to make sure that the setings are for New Zealand.

If that fails, then you can *force* the machine to your own custom settings, by changing the values.

If all else fails, then it could be Windows itself.

Please email me to let me knowhow you got on.

James
 

2 more replies
Relevance 81.59%

I'm using ms office 2003 excel. I have a column date that is in a format of 20100410 and I would like to convert it to April 10, 2010. Any ideas?
 

Answer:Solved: change date format in excel

If the column is actually a date (not text) then highlight the column and go to Format, Cells. Select the Number tab and then click on the Date format. A list of formatting options will appear and you should find what you want.
If it doesn't work, do the same thing and select Number. If you actually have dates typed in, then 20100410 would show as 40278.00 - if it is text, it will show as 20100410.00.
 

3 more replies
Relevance 80.36%

Does anyone know how to change the format of the [date] that is put into a footer?

Thanks!
 

Answer:Solved: Excel - Changing date format in footer

Hi slgraham22,

the only way is via a macro: http://support.microsoft.com/?kbid=213742

lol
Hew
 

3 more replies
Relevance 79.54%

This site looks great...thought I saw question a while back when I googled here... After reading thru many programming questions here, I realize I know lots less than I thought! ha. Think its a simple Excel formatting, but its been a while.

I've downloaded UK bank files to Excel, and need to change all dates to US format (12/6/2012 to 6/12/2012), with their corresponding text & numbers columns, and then sort top to bottom (oldest to newest), rather than how they came in (newest to oldest) to complete 2 yr. accounting summary. Excel 2007 on Windows 7 laptop.

Thanks much, guys! Jillian

I've included a sample of the columns from Excel sheet. As you can see, there are 5 columns, (oops maybe it won't show as cols) so all corresponding info has to move with the date, when I sort earliest to latest, after changing format for the 2 yrs I have to do. The format options given did not work, even tho' it IS indate format, not text, but when importing, it was already imported as US, not UK. Need to change. Not sure how to set that up, and have all info follow on corresponding cols. Thanks.

27/06/2012 20-71-64 5346xxxx -3.7 PAYMENT EAST MIDLANDS TRAI REF 177 1969574810 BCC
26/06/2012 20-71-64 5346xxxx -28.16 PAYMENT W M MORRISON PLC REF 174 7731688810 BCC
26/06/2012 20-71-64 5346xxxx -13.99 PAYMENT SAINSBURY'S S/MKT REF 174 7884931810 BCC
26/06/2012 20-71-64 5346xxxx -10 PAYMENT LUL TICKET OFFICE. REF 177 7365217810 BCC
25/06/2012 20-71-64 5346xxxx -422... Read more

Answer:Solved: Excel date format from UK to US & accompanying text files

16 more replies
Relevance 78.72%

I have a pivot table in Excel 2007 with grouped data based on the year and the quarter, so the date info looks like this:

Code:
Years Date
2009 Qtr1
Qtr2
Qtr3
Qtr4
2010 Qtr1
Qtr2
Qtr3
Qtr4
2011 Qtr1
(I know it's not really code but I didn't know how else to maintain the columns)
I have a chart based on this data but I need the dates (on the horizontal axis) to appear in a very specific format of Q109, Q209 and so on. Is there a way I can either alter what's in the pivot table to achieve this, could I use this data combined with the TEXT function somewhere else on my worksheet, or is there another workaround?

My source data is by month only and I'm using the pivot table to calculate the quarterly averages for me.
 

Answer:Solved: Excel 2007 pivot table date format for chart

8 more replies
Relevance 77.9%

Hi, I've got something that keeps going wrong.\
I have a large file which I use at work to register and plan my colleague's tasks and resgiter (outside our regular vacation/sick/abscence, etc.) the different calls they are working on allocating this. But that works, that's not the issue.
I have a sheet named "Medewerkers", yes it's Dutch for Employees of Colleagues.

I extracted and stripped this portion. (June 16: removed this attachment, correct attachment in final post)

On this sheet double clicking in Column A on the name will open a userform to edit this one or if you double click the first empty row below it you get a blank form to enter the data.
Since I regularly ran into problems useing the ....ocx calendar picker I wrote my own date picker and it works. Using son supporting code I found the funtion GetDateInLocalFormat() does it's job.

But now the issue:

The person on the sheet has as starting date 01-03-2012 (in Dutch format this March 1, 2012) and ends August 2012

On my system and at work The date pickers selects it correctly, show is and the userfrom shows it as 01-03-2012 but when I press 'Vastleggen" (Commit) the date is saved as 03-01-2012 which in Dutch January 3, 2012 when I double click again the datpicker shows the default data as January 3, 2012.

The cell's format is dd-mm-yyyy;@ the system's settings are for code 31 which is Dutch and I have pasted screen shost of the settings that show this.

I... Read more

Answer:Solved: Excel (2010) Date format issue dd-mm-yyyy and I can't find the solution!

I changed my code a littel and instead of passing the date in local format I pass it as a serial date and then there is no doubt and the result is correct. But if somebody can offer a better solution, I'm all eyes and ears, and ... maybe it will help others who struggel with date values and the international settings
 

2 more replies
Relevance 77.9%

Hello everyone:

We are trying to build an update query in excel to update one date in b2 to the date that is a h2. However instead of returning a date it returns random numbers.

Any ideas?
 

Answer:Solved: Building Update SQL Query in Excel Return Wrong Date Format

6 more replies
Relevance 73.39%

in spread sheet if I format a cell as English uk date format dd/mm/yy I have to input data as mm/dd/yy and it shows it as uk format but if I input it as uk format it does not. All my older version spread sheets and accounts when opened with excel 10 give me wrong format. I have checked that the transition mormula boxes are unchecked

Answer:date format in excel 10 will not show uk format dd/mm/yy

It is not an Excel issue.I believe what you need to do is change the System Date Format, it is probably set for US and you need to change to UK.Look here for instructions on changing the System Date:http://windows.microsoft.com/en-us/...This is for Win 7, but Win 10 should be similar.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

3 more replies
Relevance 70.52%
Question: Excel date format

I am using Excel to take data from a system that produces dates as eg 20050325 format but they are not recognised as date fields in Excel. I have tried to format the field as a date in various ways, but just get a line of ############## which doesn't appear to be a column width issue.Can anyone please help me or point to to a good help site?Thanks

Answer:Excel date format

Or is it via a text file e.g. a csv file?

10 more replies
Relevance 70.52%
Question: excel date format

Okay I am having trouble with date formatting in excel. This is what the cell has in it...2-09-1977. I need that date to be in 2/09/1977. However, if I right-click on the cell and format it as a date it turns it into a funky number. I have received this file from someone else so these cells were not formatted to be a date. Please help!!

Answer:excel date format

Can you upload, i.e. attach to a forum post, an Excel file containing such a date?  To do that, click on Additional Options... at the bottom of the forum message composition panel and use the Attach feature.

1 more replies
Relevance 70.52%

What format do I use when I type 010106 and want it to appear as 01/01/06?
Thanks.
 

Answer:Date Format in Excel

7 more replies
Relevance 70.52%

Hi, I typed 7-12 and when i press enter, it keeps appearing Dec-7. I dont want the date. i just want 7-12. I have selected "general" formatting but it still jumps back to custom "date" format. Help!

Answer:Keeps getting Date format! Excel

Start your string with an apostrophe and Excel will accept it as text. The apostrophe will not appear in the cell.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 70.52%
Question: Excel Date Format

I have run a Lottery Syndicate since the first draw (19/11/1994).Once a year I download the complete Lottery history as a .csv file. I then do a "text to columns" format and save it as an .xls file.Column A is "Drawn Date" but the date format is yyyy-mm-dd and I would like it to be dd-mm-yyyy.I highlight the column and go to Format, Cells, Custom, and select dd/mm/yyyy and OK it but nothing changes. The date in column A still shows as yyyy-mm-dd.How do I change this column please?

Answer:Excel Date Format

As the 'date' has been read from a CSV file I suspect that it is being treated as text. To convert a text value in B6 to a date you can use the following formula.=DATE(LEFT(B6,4),MID(B6,6,2),RIGHT(B6,2))

10 more replies
Relevance 70.52%

I was searching for a way to enter dates such that they are interpreted in the US (not UK) date format, i.e. mm/dd/yy not dd/mm/yy. It was unresolved in this closed thread http://forums.techguy.org/business-applications/910502-us-uk-date-format-excel.html
Since I've solved it, I'd like to share the simple solutrion with interested parties:

1. Close all Office applications.
2. Open Control Panel>Clock, Language, and Region>Region and Language>Change the date,time, or number format.
3. Under Formats, use the drop down to select "English (United States)".
4. Then close and open Excel. From this point on entering 7/1/11 will give you 1 July 2011, and not 7 January 2011.

Don't forget change it back afterwards (unless of course you're moving to the US
 

Answer:US to UK Date Format in Excel

You can also format the cells to your needs

Use the custom format
dd/mm/jjjj;@
mm/dd/jjjj;@

The ;@ forces the date to that format, you enter the date as you normally do so if it's Brittish dd/mm/yyyy and it will change to mm/dd/yyyy if the format is set to mm/dd/jjjj;@

What has to be clear for the use tis that he she has to enter the date as he knows ir should be

If they are used to entering day month year they will have to do that even though the format says different.

because February 1 2011 entered as 1/2/2011 will become 2/1/2011 in mm/dd/jjjj;@ format.

I hope I have been able to explain it,
The advantage is that you don't need to rememebr that you changed you country settings
 

3 more replies
Relevance 70.52%
Question: excel date format

Can anyone tell me how to change the format of the date in a footer. I would like in to read "Jan-27-04"
 

Answer:excel date format

If you use the Custom Footer option, you can type in whatever you like. I could not find a way to enter the data so that it updates based on your computer's internal settings,
 

2 more replies
Relevance 70.52%

Hi

I have a list of dates in Excel in US date format and I can't seem to get them to switch into UK date. I've tried the classic re-formatting and putting the data into notepad etc. Sure its pretty easy.

Any ideas?
Thanks
 

Answer:US to UK Date Format in Excel

7 more replies
Relevance 70.52%

I am working with thousands of lines of data and the date format is currently mm/dd/yyyy. I need the date to be formated as yyyymm.Is there a way to do this without typing them out again? Thank you.

Answer:Excel Date Format Help

What you want is Custom FormattingOn 2007 Home tab, Format, Format Cells, Customthen enter your format: yyyymmIn 2003 it'sFormat, Cells, CustomMIKEhttp://www.skeptic.com/

2 more replies
Relevance 70.52%

Hi We take raw data from the source system called (ariba). In the raw data we have difference date columns like (Accounting date,Paid Date,Invoice Recevied data) etc.The problem in the data is "few rows the date format would dd-mm-yy and few rows the date format mm-dd-yy.Unable to figure out this issue.Already i have tried with "text to columns" options converting this into correct date format still it is not working

Answer:Date format in Excel

i have tried with "text to columns" options converting this into correct date format still it is not workingIf the Text To Columns is not workingYou can use the =DATEVALUE() function to convert the TEXT to DATEBut this will only work on Dates that are formatted in your Operating System Default Format.In other words, if you use the American style MM/DD/YYYY as your System Date, then dates formatted in the European style of DD/MM/YYYY will not be changed correctlyand if you use the European Style the American style will not be converted correctly.So what is your System Date formatted as?I'm going to hazard a guess that you are using the European style date of DD/MM/YYYY and need to convert the American style.For this we can use the =DATE() function where we firstsplit the date apart and then put it back together in the correct order. With your American style Date in A1,put this formula in B1: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))This is for a Date with a 4 Digit Year.See how that works.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

2 more replies
Relevance 69.7%

I need to change a date format from 20120105 to 05Jan2012 in Excel. Thanks.

Answer:Change date format in Excel

First, before you do anything, make sure it is a real DATE, not a TEXT string that looks like a Date.To check, select your cell with the date Right Click your mouse and select Format CellsSelect the Number TABSelect GeneralYou should have a 5 digit number, if you do not, then you do not have a real DATE, you have a TEXT string that looks like a Date.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 69.7%

I already have dates in as 20.10.09 but I want to change them to 20/10/09 ive tried changing them with the formula but not working, I have about 500 dates to change manually if needed but wanted to know how to do automatically

Answer:how do i change date format in excel

First, this should have been posted in the office forum. That said, here goes:1- Highlight the cells (or rows) you want to change.2- go to format>cells, then I believe its the contents tab and then numbers, the I believe you need special and date. On the right pane you choose the style you want, and OK.I have used Excel fairly extensively, but currently I am using Openoffice.org at home instead of MS Office so at this moment I could not double check the exact step by step for you. If you have used long enough to use formulas, you should be able to find it with that description (sorry if I'm off on any detail). You have to be a little bit crazy to keep you from going insane.If all else fails, read instructions.

6 more replies
Relevance 69.7%

Hi, my colleague has saved a lot of our excel files and written 'Today()' (or something like that) instead of just typing the date.As a consequence, I cannot open a single file without getting today's date when I open them. I would like to be able to open the files, and that the original date is still in place.Can anyone help me?!Much appreciated!Kindest regardsCathrine

Answer:excel date format change

The only thing I can think of is to right click the file and select properties, this will show the date created as well as the date modified.You will need to remove the Today formula and type the date manually using the date created as a reference.After all there is little efort required to type 10/10/10 only 3 more keystrokes, the cell can be formatted to display it in a more informative form, ie; Sunday Oct 10 2010.

3 more replies
Relevance 69.7%

As i need to copy a big column of date in the format of 12/6/2010 from one file to another file, I don't want to copy this format to the new file. I would like to see that when I copy 12/6/2010 from the old file,the new file will appear 12-6-2010.Is there any method that can help me to do this process?

Answer:Excel Date changing format

from one file to another file,What type of files are they?MIKEhttp://www.skeptic.com/

11 more replies
Relevance 69.7%

i am having an excel sheet for bulk upload process.In that i have to enter date as a data in one column.my date should be in dd-mm-yyyy format.From 13 to 31st excel is accepting dd-mm-yyyy format.but for 01 t0 12 its taking values as dd/mm/yyyy format.so how can i set a column completely into dd-mm-yyyy format??

Answer:Hi...how to set date format for excel sheet.

Try using Custom Formatting,Your data will be stored as dd/mm/yyyy but will Display as dd-mm-yyyyJust use the Custom Format of: mm-dd-yyyyMIKEhttp://www.skeptic.com/

2 more replies
Relevance 69.7%

Hi everyone,
I have a spreadsheet acting up.. when I try to put 7/17 for month/date and the cell is formatted to date it gives me 1/0 i think... no matter what date format I put, it gives me something else.
Anyone what any idea what is happening.

thx
AMD2800
 

Answer:Excel date format is not workinig

7 more replies
Relevance 69.7%

I am trying to make a spreadsheet that has repeating dates. I need the date to repeat twice. Is there a way to do this with a formula or function? See example below. Thanks!8/1/20108/1/20108/2/20108/2/2010

Answer:How do I get a repeating date format in Excel

Hi,Put your first date in cell A1.Enter these formulas in the following cells:A2 - =A1A3 - =A2 + 1A4 - =A3Format cells with required date format.Now select cells A3 and A4.Drag them down column A as far as required and you will have pairs of dates: A
1 01-Aug-10
2 01-Aug-10
3 02-Aug-10
4 02-Aug-10
5 03-Aug-10
6 03-Aug-10
7 04-Aug-10
8 04-Aug-10
9 05-Aug-10
10 05-Aug-10
11 06-Aug-10
12 06-Aug-10
Regards

4 more replies
Relevance 69.7%

I'm trying to create an order form that will have some auto fill fields such as date, consecutive order number etc...

The first snag is the the date cell. Ideally I would like it to auto fill this field with the days date upon opening the spreadsheet, I couldn't figure that out so under 'format cell' I designated it as 'date' and chose dd/mm/yy as the format.

The weird thing is that when I enter the numbers for the date in that cell it show up as a completely different date. Example, I click on that cell and type: 041206 for todays date and the cell displays 10/24/12 ?? What the heck is that?

Thanks in advance!

Steve

Answer:Excel Date Format Is Screwy?

I have had, what I think to be similar problems and when you set the format to something specific (like mm/dd/yy) that is how you have to enter it, including the "/".

If you do it without the slash then excel interprets it differently and changes it. I am not sure why it does this or what it is doing but I do remember that typing the date exactly how the format was set ... solved my problem.

7 more replies
Relevance 69.7%

at it takes the date format only like the date() and not anything else

Answer:how do I freeze my date format in excel so th

Hi,If you want help, you will need to ask a question and provide details of:- what you are trying to do, - what you have done, - what is happening now (for example any error messages)- what data you have and- where is your data (what cells is it in)Regards

2 more replies
Relevance 69.7%

Hi there,I've tried everything I can with no specialist programming knowledge to get this done but hoping someone can help out on this one.I have a number of cells with the date format like thisSat Oct 29 00:00:00 +0100 2011in my spreadsheet - there are many, many lines so don't want to change them all manually. How do I get them into a normal dd/mm/yyyy format? I'm not interested in the time at all..Excel is clearly not recognising this current format as a date - I'm on Windows 7 with MS Excel 2010.I'm guessing I have to do a bit of VBA of some kind but would need to be walked through this quite carefully to "get" it.Any help appreciated.Thanks

Answer:converting excel date format

Very doable and vba programing not required. I will have something for you shortly is someone does not chime in before me.

5 more replies
Relevance 69.7%

I am trying to change the date format to two digit month two digit day and four digit year for example 03 08 1940 but when i type it that way i get 3 8 1940 help

Answer:how do you change the date format in excel

Format...Cells...Custom...mm dd yyyyClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 69.7%

I can't find how to change the date in my spread sheet to the following format 00/00/1999. It did work and now it is displaying what I think is the Juilian date. It is showing the correct format in the "edit" box, but in the spreadsheet and when I print it, it is just a number. (36983)

Please help.
 

Answer:Excel: Incorrect Date Format

11 more replies
Relevance 69.7%

I have a spreadsheet of invoices exported from my accounting program. I created a column and copied the invoice date to it then formatted the column with Custom, mmmm-yy. I want to create a pivot table that says for Apr-14 the total for object Code 6630 $$$$. I want to see that in April, 2014 we spent X for Food from Shamrock, Muder, and Sysco. Then what we spent in May, etc. I might not even care about the Vendor but I do want it summarized by month no matter how I do it, and I am trying to isolate our food costs.

It is not combining all of the Apr-14 lines though, but listing them separately.

Thanks
Carla
 

Answer:Excel Date Format Won't Pivot Right

i would use the group function , and just use the date field - no need to change
 

1 more replies
Relevance 69.7%

I have an existing Excel spreadsheet that contains the date format mm/dd/yyyy. I want to convert it to mm/dd/yy. Excel will not convert the current cells to the new format without re-entering the information. Can anyone help? Thanks in advance.
 

Answer:Excel 2003 Date format

12 more replies
Relevance 69.7%

I need to have a column of continuous dates like this
Thur 1/01 or if necessary Thursday 1/01. I can find other formats for date available but not this way. I need to keep the column small. I have a formula that will do this
=TEXT("1/01/04", "dddd/mm/dd") but it will not allow me to auto fill series with the day of the week changing. Can anyone Help. Thanks
 

Answer:excel 2002 date format

Try a custom format, via Format -- Cells -- Number. Category = Custom, Type = ddd mm-dd (type it in the box). Click OK.

HTH,
Andy
 

2 more replies
Relevance 69.7%

I have not found a solutions which worked, datevalue returned #value etc. The basic problem is if your system date is not according to the date format in your text date column. So if you have set it to ISO, starting with the year, datevalue will not work etc.
This procedure worked fianally:

If the date format is like '12/31/2011 (US Format)
1. Change date format in CtrlPanel Regional & Language Option to the corresponding format, in this case mm/dd/yyyy
2. Select column with dates
3. Format Cells General.
Dates will change to Datevalue 40908 (Nbr of days since 01/01/1900)
4. Format Custom i.e. ISO Format yyyy/mm/dd (which really should be used everywhere)
5. Voila 2011/12/31
6. Change date Format in CtrPanel back to your Format
 

More replies
Relevance 69.7%

Does anyone know how to change a DOB from 1945-05-14 to 05/14/1945? I have about 400+ DOB's in a column that I need formated to the second format to log import into another program.

Answer:Excel Date of Birth Format

First make a backup of the file, in case playing with date formats gives you 3548910200 instead of a date!Then check that Excel is recognizing it as a date format. I tried entering 1945-05-14 in a cell and it automatically converted it to 05/14/1945 for me. Try clicking on the cell, going to format, and picking the date format you want.If that *works* you can just select all of the cells and change the format in one fell swoop.

2 more replies
Relevance 69.7%

I am perplexed. I have an Excel spreadsheet that contains 3 columns with dates. However when I see the spreadsheet the values do not appear as dates but as decimals - for example 39302.4652777778 . However, when I click and look in the formula bar - the date appears formatted correctly - 8/8/2007 11:10:00 AM. I tried to format the columns, pasting values in a new columns and even re-entering a date itself and still get the decimal even though the formula cell indicate that it is indeed a date. Can anyone help me?

Thanks in advance
 

Answer:Excel date Format problem

8 more replies
Relevance 69.7%

It currently shows as 01/13/2013 but I need it to say 13/01/2013 - any ideas?Just highlighting the column and selecting UK date format or custom format does not work.

Answer:I have a date in Excel I need to change the format

In your other post in the Windows 7 forum you responded that the Custom Format did not work.Are you sure the data is really a DATE, not TEXT that looks like a Date?Select the cell with the Date and Format it as General or Number.If it is a real Date you should get a five digit number of 41287if it is TEXT it will retain the appearance of a date.MIKEhttp://www.skeptic.com/

4 more replies
Relevance 69.7%

I am trying to combine information from a couple cells to create a date header for a column in a worksheet.

I want the cell to read:

Date: Thursday October, 21 2003

I can get the first two parts but the date comes back as a number:

Date: Thursday 37917

Here is the formula:

= "Date: " &'[WKH Daily Roster.xls]Sheet1'!$C$1& '[WKH Daily Roster.xls]Sheet1'!$D$1

The last part is the date "$d1$1"

I know this is probably simple but I searched help in Excel and on Micros.. site with no luck.

Help!!!
 

Answer:Format date within Excel Formula

Something along these lines?

= "Date: " &[Book1]Sheet1!$A$1&" "&TEXT([Book1]Sheet1!$A$2,"mmmm, dd yyyy")

HTH,
Andy
 

3 more replies
Relevance 69.29%

My DAQ system outputs the time and date for each data point collected in the first column along with the various signals in other columns, and I have at least 4k data points in each column. Time and data is given as shown below.11:54:23:11 - 23 MAY 1011:54:38:11 - 23 MAY 10I am trying to extract the time diference between the two data points by subracting the info in two cells, and obviously Excel does not recognize the info in the cell because of the presence of ":11" after hh:mm:ss info.How would I delete ":11" from the column and convert the given info to a Excel readable time/date format so that info in two cells could be subtracted to get the time difference.Thanks

Answer:Time and Date Format in Excel 2007

Are the Time & Date together in one cell?If the times are in there own cells, then Custom Format as:[h]:mm:ss.00This will allow you to keep the full time.MIKEhttp://www.skeptic.com/

7 more replies
Relevance 69.29%

All my Excel 2010 files on Win 7 64 bits have had their date formats changed from yy-mm-dd to yyyy-mm-dd; formats that were previously yyyy-mm-dd have been left unchanged.This is very time consuming as I have to do format cells changes on every field of every tab of every file affected; because of the space factor, I need to use the shorter format.I have woken up after spending hours on manual format cells changes and have thought of checking if my short date format in regional date formats had been changed...sure enough; I have made the correction and everything is back to normal.The change must have been made by Windows update between 13/3/22 and 13/3/25; the only time I change formats is when I install a new version of Office (October 2010 in my case).

Answer:excel 2010 loses date format

Since everything is back to normal, I assume this is just an FYI, correct? I didn't want to leave your post as "Unanswered" so I am posting this response.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 69.29%

I am trying to use data such as Day and Month as Values,
I have a serial date value and although I can format this to mm or dd and get the desired number, this cannot be used for further calculations because when called i get the whole serial value

ie: .date is 22/11/2009, Serial date i: 40139
if I format month as Date; mm the numbers appear as 11
if I format day as Date;dd the numbers appear as 22 as expected,
if I try to recall value of a cell I get 40139 for both instances....

Anyone got an answer? or know how to dissect serial date to get Years, Months and days as individual values

enjoy the brain teaser and thanks to all who try find the answer
 

Answer:Extracting from serial date format in Excel

OK. Put date either serial or date format in a cell, for instance (A1)
then where required put: =DAY(A1) and the result will be the day assigned to 40139 ie 22 if formatted as a number or Sunday if formatted dddd
the same for month, ie =MONTH(A1) the result vill be 11, or Nov, or November depending on how you format the cell..

Thanks again Chuck, Keep working on your other problems.......
 

3 more replies
Relevance 69.29%

We have spreadsheets that many people use, and for two of us, all the date fields randomly changed to yyyy/mm/dd format. While we can manually change how it appears by using the format option, we still have to actually type the date in as yyyy/mm/dd. If we type 10/11/12 it changes it to 2010/11/12 instead. We have to type in 2012/10/11 to get it show up as 10/11/12 instead. One user is on version 2010 and one user is on 2007. Other users with 2003, 2007, and 2010 that use these same sheets are able to see and type the dates in the regular formatting of mm/dd/yy like it used to be without a problem. How do the two of us that can't do that anymore fix it?

Answer:Excel date format changed to yyyy/mm/dd

First thing that come to mind is to check your Windows System Date and see how that is configured.It should be in Control Panel, Date & Time.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 69.29%

I have a group of cells in a column that I would like to "format" individualy/independantly of one another and worksheet date (seperate cell to drive other functions) to change colors (the entire cell) red, yellow, green based on the number of days elapsed from the date in the cell NOT the date on the worksheet. For example cells D52 through D61 all contain different dates. I would like the cell to fill (based on their dates) green if it is greater than ten days from the date in the cell, than yellow if it is the tenth day from the date in the cell and red if it is greater than 10 days from the date in the cell. Is this possible? I 've been scrubbing numerous sites and such and "googling"- no luck. If so, It just wasn't dumbed down enough for me to comprehend. Any help would be appreciated. Thanks a lot.
 

Answer:Excel 07 conditional format? (Date functions)

Hi wads,

I have a case statement that may help you along. This changes the format of cells within a defined range. myplage2 is the range, in the sample it is named. You can change it to the rc:rc format if you need.

The event should be loaded in the worksheet change. Then every time you submit a change to the worksheet it will update the formatting.

Here is a code snip:

Set myplage2 = Range("bupercent")
For Each cell In myplage2

Select Case cell.Value
Case 95 To 100
cell.Interior.ColorIndex = 4
Case 85 To 94.99
cell.Interior.ColorIndex = 6
Case 75 To 84.99
cell.Interior.ColorIndex = 46
Case 0 To 74.99
cell.Interior.ColorIndex = 3
Case Else
cell.Interior.ColorIndex = xlNone

Stoneboysteve
 

6 more replies
Relevance 69.29%

Why are some of my dates changing (the month and day are swapping places) when I have a pdf file and do a file - save as - spreadsheet - Microsoft Excel Workbook???Some of the dates convert correctly, but some of them are not. Such as...9-11-2014 will show up as 11-9-2014...

Answer:Date format incorrect when I convert from pdf to excel

Since I don't have the ability to save pdf's as Excel spreadsheets I can't test the proces. The best I can do is speculate.I also don't know by your examples what format your examples are in. You say the month and day are swapping places, but I don't know if the original (9-11-2014) is supposed to be mm-dd-yyyy or dd-mm-yyyy since 9 and 11 could be either a month or a day. It would be interesting to see what would happen if the dates in the pdf were 11-22-2014 or 22-11-2014, depending on which format they are using in the pdf.In any case...For the dates that are changing to a different format, it is possible that your system is converting them to correspond with the format set within your regional settings. Excel may be assuming that e.g. 9-11-2014 is mm-dd-yyyy but your regional setting is dd-mm-yyyy so Excel converts it for you. It's just trying to be nice. ;-)For the dates that are not changing, it is possible that Excel is not actually seeing them as dates but rather as text. It is a well known annoyance within Excel that dates imported from "outside sources" (web pages, databases, etc.) are not always seen as dates by Excel. Changing the cell format to one of the date formats doesn't help, since Excel doesn't consider the contents to be a date. If Excel is not seeing some of the "dates" as dates, then it won't convert them to match your regional settings.Sorry, but that's the best I can offer at this point.Click Here Before Posting Data or VBA Code ---> How To Post Data ... Read more

3 more replies
Relevance 69.29%

I found the way to convert the date in my favourable style but i have a problem in entering the date as MS-EXCEL takes date in mm/dd/yyyy format but i want to enter the date in dd/mm/yyyy style.plz helpthanksAnupam dev

Answer:how to enter date in MS-EXCEL (indian format)

Format Cells...Custom...dd/mm/yyyy Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 69.29%

how to change the date format form dd/mm/yyyy to mm/dd/yyyy.I already tried by using >format cell > number tab > date , but it is not working. Solution for this will be highly appreciated.Thanks in Advance.

Answer:how to change date format in excel sheet

What do you mean "it's not working"?Are you trying to change the default for your system or just for some cells in Excel?To change the default for your system you would change it in the Control Panel under Regional And Language Settings.Perhaps we need some more details.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 69.29%

This is a continuation from this previous thread I started but is more specific to changing one more thing before I export the worksheet to a tab delimited file.

Need to add one more thing to this script and it has to do with the way the date is formatted in some of the cells. The cell gets exported as to what the cell format is versus what the true cell value is.

So for instance. The actual cell value that is typed into the cell is 12/23/2013 but the client put in a custom format of d-mmm so it displays as 23-Dec and that is how it gets exported to the tab delimited file. I need the cell to export to the tab delimited file as 12/23/2013.

So I tried this code to change the Date Formatting but this did not work.

Code:
If IsDate(rCell.Value) Then
rCell.Value = DateValue(rCell.Value)
End If
Anyone else got any ideas.
 

Answer:Excel Date Format Change with Vbscript

9 more replies
Relevance 69.29%

in excel when i type a date ie..06/10 it shows as 9/1/1901 i have tried changing the format cell but that doesnt fix it. is there another format or formula i need to find to fix this

Answer:excel 2010 help needed for date format

Where are you located, US or Europe/Asia?Is your default date: 12/31/2011 Month Day Year ?or 31/12/2011 Day Month Year ?I do not know how you are getting 9/1/1901 from entering 06/10,but you can try a Custom Format and see how that works.Custom FormatRight click on the cell with your DateSelect Format CellsSelect Custom, at the bottom of the popup windowIn the small input box, under the word Type:delete what ever is in the box and enter:MM/DD for Month Day type of dateorDD/MM for Day Month type of dateClick OKAs long as your cell contained an actual date then you should see it change.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 69.29%

In Excel for Mac 2011, I have a column formatted General with information like this: 5/31/2011 7:02 PM PDT. How can I remove the time, then change the date to the format yyyy/mm/dd, so I can sort by date? (Maybe all I have to do in order to sort by date is remove the time? If so, how?)Thank you,David

Answer:Change Excel text format to date

Try this:With your data in cell A1First, Custom Format cell B1 as yyyy/mm/ddNext, in B1 enter the formula:=DATEVALUE(LEFT(A1,FIND(" ",A1,1)))MIKEhttp://www.skeptic.com/

5 more replies
Relevance 69.29%

I have an excel spreadsheet with a date column. The column format is *3/14/2001. I enter 08/01/2011. The formula bar reads 8/1/2011. The cell reads 40756. If I put the cursor in the formula bar the cell reads 8/1/2011. Place the cursor any place else cell revers to 40756. How can I get the date to display as a date in the cell.

Answer:excel 2010 date format problem

One of your options is messed up:Click on the Office Button in the upper left cornerClick on Excel Options, down the bottom of pop upClick on AdvancedScroll down to the section that reads: Display Options for this Worksheet:UN-Check the box that read: Show Formulas instead of their calculated results.Click OKThat should correct your problem.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 69.29%

For some MS Office buff. Have workbook with 4 sheets with similar tables of data on each sheet.This is a "brand new" document.Sheets labelled FEB / MAR / APR / MAYNow if I format the date fields on all sheets except MAR as dd-MON-2007 ( eg 12-Apr-2007 ) format takes OK. If I attempt to format date fields on MAR sheet ( eg 05-Mar-2007 ) I get ######## returned as the value ? Very peculiar indeed. Any advice ?

Answer:Excel 2000 - date format query

Make the column wider.

3 more replies
Relevance 69.29%

Hello, I know how to change 'output' format of dates on ExcelHowever, Does any one know how to change the 'input' format of a date ?--> i.e. when I enter 5/10/09, the computer understands 10-May-09 instead of 5-Oct-09thx

Answer:Excel: chge 'input' date format MM/dd dd/MM

Excel uses the settings found here:Control PanelRegional and Language OptionsRegional Options TabCustomize ButtonDate Tab

3 more replies
Relevance 69.29%

I would like to format excel cells with conditional formattingIt is a column of dates and I'd like to determine the following If the date in the column is less than today +30 daysIs the formula i'm trying to make, but it does not work =/ Any assistance would be greatly apprecited.if = (today() < +30)

Answer:Excel: If Date is within 30 days format cells

How you want to format? what is "=/"?

4 more replies
Relevance 69.29%

Hi again all,

Need a little help and this time I think it is possible I just lack the computer know how to pull it off.

I have attached a summary spreadsheet so I can reference as I go but I will give a little back story first. I want to create a spreadsheet that I can view and at a glance see who is traveling on a particular date and when they are due back. The problem is that the personnel will travel several times a year and I don't want to just create a giant spreadsheet with loads of dates.

Cell A3 is just the persons name and serves as no consequence for what I am asking.

In cell B3 and C3 I want to be able to display when the person is NEXT traveling and when that person is NEXT due back. Now this is the tricky part, I want this to automatically change the value of the cells after the date has passed.

In E and F I have put example dates. So lets say the 10th of July passes I would like the Outbound to change to 20th of Aug, and so forth. The same would apply for the inbound.

My thoughts were I could just dump all the dates at the bottom of the spreadsheet off the page break and have the text white so it is not visable and the sheet will be neat and tidy. There will be several people to track and I dont like clutter.

If anyone is particularly clever, could I even host all the dates for cells B3 and C3 on sheet 2 of the spreadsheet??

Once again, any help would be greatly appreciated.

Thanks & Regards
High
 

Answer:Format a cell in excel depending on date

8 more replies
Relevance 68.47%

i generate an xhtml , with date formats mm/dd/yyyy (eg. <date> 4/22/008</date>) and open it as an excel.
when i send the xhtml to japan,and the japanese open it as excel ,they see the date fields as dd/mm/yyyy .
however , I want everyone to see the report in my mm/dd/yyyy format.

what should i put int the <date> </date> tag in the xhtml so that it will it will open the same for evey location
 

Answer:problem with date format in excel, when it is open from different locations

Excel has it's own International setting for date formats so it displays as is chosen in the Excel "Setup". I don't know much about xhtml but you could try surrounding the date in text quotes to con Excel in to thinking it is text and therefore leave it's format alone.
 

2 more replies
Relevance 68.47%

Is there a way to save a custom date format in Excel besides saving it in a spreadsheet and using that spreadsheet as a template?

Thanks.
 

Answer:Saving a Custom Date Format in Excel 2007

Click on the blue "?" (help) button in Excel and type "custom date" as a search term.

From the results, choose "Create A Custom Number Format". Scroll down and you'll see the options for creating custom date formats.

Hope this helps.
 

3 more replies
Relevance 68.47%

Hopefully someone can provide a quick & easy solution for meI'm getting a report from a web based app which outputs a table of login and logout dates and times into an Excel spreadsheet.The dates are in US format with no leading zeros and have a text delimiter e.g. '8/18/2010 9:05:47 AM'6/9/2010 11:51:07 AM'11/26/2009 2:06:38 PM(By the way, the date is set as English in the Preferences and XP & Excel are setup as UK English)I have to provide a "Monthly Usage Chart" so I really need to convert this date to UK format (e.g. 07/12/2010) and then convert the date to a Month and Year which I can then chart up.I can do various conversions to date value, and split up the date then concatenate it back into UK format but it's messy and inconsistent due to the 'no leading zero' issue.Anybody any idea of a function or a piece of code to output the date in UK format or output Month and Year please?Thanks in advance

Answer:Excel Date Format Problem - conversion wanted

In a spare column=MID(A1,2,FIND(" ",A1)-1)and copy down. Then copy those results then Edit > Paste Special, tick Values and click OK.Then select those results, Date > Text to Columns, click Next twice, tick Date and select MDY then click Finish.You can then use MONTH to get the months.

9 more replies
Relevance 68.47%

I have a list of events with dates that I want to enter in an excel worksheet and my problem is that the dates are not formatted the same:

-some are full dates and enter fine (day, month, year)
-some are month year only
-some are year only

Does anyone have any idea how I might get these all into one worksheet and be able to sort them?

I've read all the Microsoft stuff on how excel codes dates from Jan 1, 1900, so I'm dubious that there is a solution, but I would love to hear any ideas that might work or a suggestion for a workaround or other application idea for collectiing this data. It's a large project for a charity and they know less about excel than I do <g>.

thanks,

Tom O'Connell
 

Answer:Excel date format - multi formats allowed?

9 more replies
Relevance 68.47%

Hi all,

I would like to conditionally format the attached spreadsheet so that each alternating week is shaded on whole set of rows.

Also with our roster the week runs Thurs to Wed.

I've looked quite a bit into conditional formatting now. But this one has me stumped.

Thanks for any advice.
 

Answer:Excel: Format rows based on date and a few other specifics

7 more replies
Relevance 68.47%

Hi all

I created an Excel spread sheet that the user has to input among other things many dates. The spread sheet that I created has many users doing their share of inputting data. The problem is not the formatting of the dates, but how the date is entered into a cell. I have formulas that use the dates to calculate the days between dates and other date type functions. For example, if someone enters Jan. 15, 2006, the date is entered as a text and the formulas return a #Value. I have seen many types of date entries that return a #Value in many cells.

Is there a way that I can warn the user that he or she has entered the date in the wrong format in a MsgBox?? If someone out there has a macro that can accomplish one of the above, that would save me many headaches. The dates are in specific columns (J and K)and are not random throughout the spreadsheet.

Thanks again,

Mario
 

Answer:Correct Date format in Excel spread sheet

You can use Data > Validation to warn them (although if they copy/paste, that won't retain what you want).

Perhaps the best is to use a UserForm which can control input and formatting. That way they can not change it even after they enter the data.
 

2 more replies
Relevance 68.47%

I have a members database supplied as a query of our web based database. In three columns I have dates - date joined, date of birth and record updated. In order to inform my fellow members of the records we hold and allow them to amend them I have merged a letter into word.If the cell is blank it appears as 12:00:00AM. Some of the dates that do appear for example: 11/22/2004 which should of course be 22/11/2004. In one of the fields it returns 1/18/2005 3:15:54AM (Our Administration Officer was either working late or getting up early) Note the Excel field in this example just shows date not time)I have tried formatting the columns differently but I haven`t found a combination that works. Help please!

Answer:Excel Date Format and Word Merge Problem

I've just tried this with some random dates typed into a worksheet and it appears to work perfectly!My dates are formatted as Date - Type 14/03/2001.They appear correctly in the merged Word document; blanks appear as blanks.So I'm a bit baffled.Office 2003 by the way.

10 more replies
Relevance 67.65%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 67.65%

Hi

Can someone show me how to covert a date e.g. dd/mm/yyyy so that it reads as mm/yyyy only and so that a pivot table doesn't recognise the orignal long date of dd/mm/yyyy.

Tried using custom to mm/yyyy but pivot table still reads the date as dd/mm/yyyy.

Thanks

NCFC (Newbie)
 

Answer:Excel 2007 - Change date format for pivot tables

hi NCFC,

Don't know about Excel 2007, but in 2003:

Right Click the Pivot Table,
Field Settings,
Select the field name that holds the dates,
Number,
Choose your format.

lol
Hew
 

2 more replies
Relevance 67.65%

I imported data from another program into Excel and it is giving me the date and time in this format:10012015 01:40I need it to look like this:10/01/2015 01:40The data is in Cell R72. When I use the formula below, it comes up as 8/15/03 1:40 instead of 10/01/2015 01:40.=DATE(MID(R72,1,4),MID(R72,5,2),MID(R72,7,2))+TIME(MID(R72,10,2),RIGHT(R72,2),0)Any help would be appreciated!message edited by rajasperson

Answer:Convert text string to Date and Time format in Excel

Try this:=DATE(MID(R72,5,4),LEFT(R72,2),MID(R72,3,2))+TIMEVALUE(RIGHT(R72,5))The syntax for the DATE() Function is: =DATE(Year , Month , Day)The Year starts at the 5th Character and is 4 Characters longFor the Month we get the First Two characters from the LEFT() side of the text string.For the Day we start at the 3rd Character and get 2 characters.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

3 more replies
Relevance 67.65%

in my data source the date field is custom ddd mmm dd yyyy

When I create the pivot table it shows as dd-mmm but when I try to format the field it already shows the format correctly but the sample show dd mmm

ie. Source table shows Thu Mar 26 2015
Pivot Table Shows 26-Mar

I am unable to reformat it to show the source.

So how do I get the pivot table to display the right date format?
 

Answer:Excel Pivot Table Date is in wrong Format and I can't get it to change

Have you definitely ensured all cells in that column are formatted the same in the source table?
I formatted the current date as you describe and created a pivot table with that data and Excel automatically used the same formatting. I'm using Excel 2013.
 

3 more replies
Relevance 66.83%

why has the recently installed Windows 10 changed my date format in my Excel documents??

More replies
Relevance 66.83%

Hi all

I'm trying to find out if there is a way to display a column of dates in Excel as DD-MMM-YYYY (ie 01-JUL-2010). The standard formats only capitalise the first letter and it's driving me crazy!

Any advice will be gratefully received
 

Answer:Excel - Can the date format dd-mmm-yyyy display the month in upper case?

Welcome to the forum

Try this

 

3 more replies
Relevance 61.91%

Can anyone give me a hand with this? I have imported some data into Access with dates written in the form mddyyyy (for Feb 14, 2005 it gives 2142005 and for Dec 25, 2006 it gives 12252006) i.e. they can be 7 or 8 digits long and are not formatted. The field is [date needed].

What I would like to do is show the records containing the date on a form sorting them in reverse chronological order. I set up a query to get the data from the table and set up my subform and then it seems I would have to dissect the date into year, month and day portions and sort each of them. [Maybe there's a way to handle dates like this built in but I do not know] It's easy enough to get the year with the right function as in Year: Right([date needed],4) but the days and months are a little more difficult since the leading month can be either 1 or 2 digits. Note that the days are always two digits i.e. 01 for the first of the month. If the month is 1-9 then there are 7 digits in the date and the month is the first character and the days are characters 2 and 3. If the month is 10, 11 or 12 then the month is the first two digits and the day the second two.

My question is whether this is the right way to do it and can a query field use some sort of if/then/else statement with the len function. What would it look like? If this could be done then I could use the Left function to get the month (i.e. first digit only when len = 7 and first two digits when len=8) and the mid function for the da... Read more

Answer:Solved: Date Format in Access

8 more replies
Relevance 61.91%

Having problems with the date format on our Citrix Server

Problem Details:
Office Apps are not using the System Default Date Format.
Regional Options show English (United States) as the locale - dddd, MMMM dd, yyyy as the long date format and that's what we want. However, when you use Word or Excel it keeps defaulting to English UK settings
E.g Thursday, 10 January 2008

This only seems to be affecting MS Office though

Setup:
Windows 2000 SP4 Server
Citrix Metaframe XP FR3
MS Office Professional 2003 SP2

Recent Changes to System:
Server was in an NT4 domain and was just migrated to an Active Directory Domain. Users are still logging in with their NT4 domain credentials though (problem still exists when I login with my AD credentials).

Things I have checked:
Regional Settings
Registry Setting (HKCU/International)
Any ideas?
 

Answer:Solved: Office Date Format

Wonder if this is coming from the "Microsoft Office 2003 Language Settings" that are located in Start>All Programs>Microsoft Office>Microsoft Office Tools...

I played around and found I could force that date format if I set language to English UK.

Hope this helps,

MBN
 

2 more replies
Relevance 61.91%

Hi,

I have a small php script which reads records out of a MySQL table and displays them as an HTML page, by date order.

It's easy to make the dates come out as 03-05 or 04-15. I want to show them as Mar 5 or Apr 15.

Here's the short script with my notes as to what works and what doesn't:

<html>
<head>
<title>Events List</title>
</head>
<body>
<h1>Upcoming Events</h1>
<?php
@ $db = new mysqli('localhost', 'xxxxx', 'xxxxxxxxx', 'rsvp');
if (mysqli_connect_errno()) {
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
$query = "select Title, Date, Notes from events where Date >= '2010-03-22' order by Date ";
$result = $db->query($query);
$num_results = $result->num_rows;
for ($i=0; $i <$num_results; $i++) {
$row = $result->fetch_assoc();
echo "<h2>".htmlspecialchars(stripslashes($row['Title']))." - ";
$datetime= substr(htmlspecialchars(stripslashes($row['Date'])),5);
//
// echo $datetime; works OK at this point but gives 03-02, 04-15, etc.
//
// Next line was to give date as Mar 3 or Apr 15, etc.
//
// echo date_format($datetime, 'M j') ;
//
// Instead it gives various errors as I try quotes, double quotes, etc., etc.
// Mostly:
//
// Warning: date_format() expects parameter 1 to be DateTime, string given ... on line 22
//
//
// Tried lots o... Read more

Answer:Solved: PHP Date read and format

This did it:

for ($i=0; $i <$num_results; $i++) {
$row = $result->fetch_assoc();
$dt= substr($row['DateTime'],0,10);
$new_date = date("Y-m-d", strtotime($dt));
$prnt_date = date('M d', strtotime($dt));
if($new_date >= $start_date) {
echo "<h2>".htmlspecialchars(stripslashes($row['Title']))." - ";
echo $prnt_date . "</h2>";
echo htmlspecialchars(stripslashes($row['Notes']))."<br>";
echo "</p>";
}
}

Actually, since the query was already for date >= today's date, the logic loop is not needed...but it took me the day to realize that the error (mine, of course) was I was comparing a date formatted as '2010-03-24' with a date formatted as 'Mar 24' .
 

1 more replies
Relevance 61.09%

Hello,

For some unknown reason, the "Short Date" format in my Excel installation changed some months ago.
When I type 8/11 into the cell of a new worksheet, i get "08 11 2015" displayed. This format corresponds to the "Short Date" format in Excel, as you can see below.

However I never wanted this, as I prefer "8-Nov-15" to avoid confusion with USA standards.

The asterisk tells that the format derives from the OS. This is not true as my OS says "ddd d-mmm-yyyy" as you can see below. So it DOES NOT derive from the OS.

Can someone explain this strange thing and how I can align them again, or at least set it back to "d-mmm-yyyy"?

Just to make sure: I need to change the "Short Date" format and not the format of just one particular cell...! So I find it back as I want it, tomorrow when I start Windows again

Thank you,
Stefano Gatto
Geneva, Switzerland






 

Answer:How to change the "Short Date" format in Excel?

Hi Stefano.
I am sharing a link of support.office.com for changing date format all the way you want.
I hope it will be helpful for you.
https://support.office.com/en-us/ar...you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
Good Luck.
 

7 more replies
Relevance 61.09%

date/time format using d hh:mm How to suppress 0 days?

Answer:Excel date/time format as days hh:mm and suppress 0 days

As far as I can tell, there is no way to suppress the Zero using that Custom Format.You could probably process the data and remove the Zero but you will need either a formula or VBA.Is this the result of a calculation?MIKEhttp://www.skeptic.com/

2 more replies
Relevance 61.09%

Encountered a small but irritating problem between Access 2000 and Access 2003. Designed a table and input form in Access 2000 that contain a date field to capture credit card expiration dates, which are always only month and year. Made the format "m/yy" with an input mask of #0/00;0;_. Works perfectly in my Access 2000, but when I installed the application on a client's (non-profit org.) computers, which have Access 2003, every entry of a year other than this year (06) gets changed to 06. E.g., if the user enters 0508 and tabs to the next field, it displays "5/06". It behaves as though it thinks you entered only the month and DAY, but since the format has to be m/yy, it displays the 5 and tacks on the current year.

Access (at least Access 2000) does not offer a built-in month/year format. So how can I make the field understand that what goes in there is supposed to be only month and year? Is it possible to do with a Date/Time field type, or do I need to make it Text, or what?
 

Answer:Solved: Month/Year date format

i'm a surprised on the M/YY format - how doesOct Nov Dec work 10/11/12
should the format be MM/YY
 

3 more replies
Relevance 61.09%

Hi All

I'm trying to save a selected range of the excel spreadsheet as .csv file with a macro, all works fine but the dates are converted to a number eg 4/4/2004 becomes 38081.

the code i'm using is:

Const Folder As String = "C:\Clients\test"
Dim FileName As String
Dim LastCell As String
FileName = Folder
ThisWorkbook.Worksheets("test1").Range("A1:k500").Copy
Workbooks.Add
ActiveCell.PasteSpecial (xlValues)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=xlCSV
ActiveWorkbook.Close Savechanges:=False
Application.DisplayAlerts = False

Anybody knows a workaround?

Thanks
Nick
 

Answer:Solved: Macro change date format

6 more replies
Relevance 61.09%

Hi. Imported table has dd:mm:yy dates in two fields, illegible to Access. So I'm trying to change these to dd/mm/yy.
I'm getting syntax error (missing operator) in the strings. Any ideas?

Dim Dobdate As String
Dim Labdate As String
Dobdate = "UPDATE [New Non Trait Data] SET [Dob] = [0-9][0-9]/[0-9][0-9]/[0-9][0-9] WHERE [Dob] = [0-9][0-9]:[0-9][0-9]:[0-9][0-9] "
Labdate = "UPDATE [New Non Trait Data] SET [Lab date] = [0-9][0-9]/[0-9][0-9]/[0-9][0-9] WHERE [Lab date] = [0-9][0-9]:[0-9][0-9]:[0-9][0-9] "
DoCmd.RunSQL (Dobdate)
DoCmd.RunSQL (Labdate)
 

Answer:Solved: Access date format change

You do need VBA to do this, you can use either an Update query where you split up the incorrectly formatted date and put it back together with the correct "/" in place or you can just use a simple replace Query to replace the ":" with "/".
 

3 more replies
Relevance 61.09%

Hello,
For the past week or so my date display has changed, and I think it has to do with XP rather than individual programs because it's changed in two programs, Microsoft Money and Outlook Express. I want my dates to be displayed as month/day/year, but instead they are now displayed as day/month/year. Call me simple, but it's confusing and I would like to change it back but can't figure it out. Thanks!
 

Answer:Solved: Change Date Format Setting

7 more replies
Relevance 61.09%

Hi to All!

I have a database of vendors. 2 of the fields are "Exp_Month" & "Exp_Yr". The data for "Exp_Month" is entered as "JAN" etc. The data entered for "Exp_Yr" is "01", etc. This was uploaded from a program called ADPICS and imported into Access.

I am trying to generate a report that will give me all vendors who will expire in 90 days. I was tying to make this happen in a query then would base my report off the query. But I'm having trouble with proper syntax for the date and year.

Will someone please help me??? THANKS!!!!!
 

Answer:Solved: Access 03 - Date Format in Query

ddw, what format are the 2 fields in are they "Date" fields, Text or Numbers?
 

3 more replies
Relevance 60.68%

Is it possible to add space(s) between the date and the time?
i.e. instead of 19/06/2012 16:55, I'd prefer: 19/06/2012 *** 16:55

(* = Space. The editor is removing the spaces).

Thank you.
 

More replies
Relevance 60.68%

Hey everyone,

I'm having problems with Access, where I have a date/time field, which stores a month and a year (successfully), but still automatically assigns the first date of the month in question.

I used the following input mask (00/0000;;_) for the date field, and so far I haven't had any troubles with putting in a month and year. So far so good. But when I complete the date and move on, Access changes it to a full date. e.g. 06/2010 --> 01/06/2010.

Now, I'm actually fine with this part, my only problem is that I don't want it to do this in forms (both datasheet and normal view) or reports.

I heard something about using a Format (http://office.microsoft.com/en-us/access/HP010990151033.aspx) to adjust the display, but I can't seem to get that to work. Is there any other way to tell Access how to display the date field, like what can be done in Excel?

This is rather urgent as I have a project due tomorrow and this problem (as well as others) have now been reached and I'd like to solve them as soon as possible.

Any help is appreciated, and I look forward to your responses.
 

Answer:Solved: Urgent: How do you format the DISPLAY of a date field?

8 more replies
Relevance 60.68%

Hello,
I have a column of dates that I have formatted like this:
\S\Ayymmdd
The date needs to look like this:
SA160327
This is exactly what I need, except I have to insert these in a csv file, and when I try to copy and paste the date, the formatting is gone, and I just get numbers like this:
42456
Does anyone know how I could copy my dates so that they're just text but in that format?
I'm currently retyping all of them, and I have hundreds....
Any help is much appreciated. Thanks in advance.
Fern.
 

Answer:Solved: Converting custom date format into text

8 more replies
Relevance 59.86%

Good day guys

My puzzler today (and has been for ages) is this:

In my Access report, I pull in "StartDate" and "EndDate" fields from the calling form and wish to display them as part of a report subtitle.

I therefore have a field called "DateDetails" in my report header whose ControlSource is:
Code:
="Records Selected between " & Forms!Reports!StartDate & " and " & Forms!Reports!EndDate
The dates actually derive from a table and the date format for both fields in that table is "General Date".

The result is that the report shows the following as an example:
Records Selected between 17/05/07 12:00:07 and 10/08/07 12:00:07

BUT what I want is the following:

Records Selected between 17/05/07 and 10/08/07

or even nicer

Records Selected between 17-May-2007 and 10-August-2007

Can this be done in such a control?

Chris
 

Answer:Solved: Access 2003 - Date Format in a Calculated Field

7 more replies
Relevance 59.86%

All of a sudden some of the PCs in my department quit recognizing what I thought was base Word 2003 VBA... Below is a simple example. (BTW: All PCs in my dept use Word 2003)

On my PC, this code works as it should, and the MsgBox displays a set of quotation marks (")
Sub quo()
qut = VBA.Chr(34)
MsgBox qut
End Sub
​On other's PCs, I get an error, with qut highlighted: “Can’t find Project or Library”
In essence, this PC doesn't understand "CHR" and commands me to define it.
It also doesn't recognize LEFT, DATE, FORMAT, and who knows what else that I haven't found yet.

I've been able to force it to work on Date by changing:
z = Format(Date, "mm/dd/yyyy")
TO
z = VBA.Format(VBA.Date, "mm/dd/yyyy")

But I can't force CHR to work at all.

Is there a setting somewhere? I don't get it... all of a sudden this happened...
HELP!
~maruapo
 

Answer:Solved: Word 2003 VBA not recognizing CHR, LEFT, DATE, FORMAT

Press ALT + F11 to open the VBA editor and then click TOOLS >> REFERENCES and let us know if any references are missing.

Rollin
 

2 more replies
Relevance 59.86%

Hi,

I want to find and replace the Date format in word, if there is any macro coding for this. I try to using the following codes

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " 7/"
.Replacement.Text = " 07/"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

But "7" is relates to month, so if there is any code for change the numeric character to specific date format as "mm".

For ex :

BUARDC 04294008295 010321 6000 7/17/2010 0000354652 000224

The above is the example data , in that month needs to added a leading zero.

Thanks in advance
 

Answer:Solved: Find and replace the date format in Word file

16 more replies
Relevance 59.45%

I am trying to create a template to calculate the expected number of days for my shipment to arrive and I am cracking my head trying to put in the day as well i.e. for today [Aug-5-2002 Mon] How do I format it?

Anyone can help me? Thanks.
(btw, is it possible in the first place?)
 

Answer:[SOLVED] Day & Date in Excel 97 SR2

Right click the cell and select format cells, select custom from the list and put in mmmm-d-yyyy dddd and the resulting date will be in your format.
 

2 more replies