Microsoft Office Forums Date formatting

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-07-2011, 10:54 PM
kjxavier kjxavier is offline Date formatting Windows XP Date formatting Office 2007
Advanced Beginner
Date formatting
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Exclamation Date formatting


Formatting Problems:

1. I have a date field E.g.: January 02, 2011 and i want this Date formatted into 01/02/2011. and when i format the fields using mm/dd/yyyy it gives the exact result. but, when i take this to another program or if i compare it with another excel sheet. the date field is shown as 1/2/2011

2. In another field i need same kind of date format to be converted to Jan-11 (Mmm-yy) but when compared this too gives similar result(1/2/2011) and my comparison shows ERRORS.

is there any way i can solve this.
can i make this permanent or static or something like dat so that it will remain permanent to the format i specify.

Rgds

Xavier

(Note: please check with the fields "D" and "AD" in the attached file)
Attached Images
File Type: jpg 1.jpg (12.0 KB, 1 views)
File Type: jpg 2.jpg (18.6 KB, 3 views)
Reply With Quote
  #2  
Old 08-08-2011, 03:39 AM
OTPM OTPM is offline Date formatting Windows 7 32bit Date formatting Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Xavier
This is happening because the date formnat in the other program is set differently. For example of your documents are based on different templates they may have differing date formats and this is what is causing the issue.
Hope this helps.
Tony(OTPM)
Reply With Quote
  #3  
Old 08-08-2011, 05:33 AM
kjxavier kjxavier is offline Date formatting Windows XP Date formatting Office 2007
Advanced Beginner
Date formatting
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default Date Formatting

Quote:
Originally Posted by OTPM View Post
Hi Xavier
This is happening because the date formnat in the other program is set differently. For example of your documents are based on different templates they may have differing date formats and this is what is causing the issue.
Hope this helps.
Tony(OTPM)
Hi TONY

Please check with the File attached.

The fields display the desired format. But still the Formula Bar displays the format of Excel (org) maybe and that format displayed on the Formula bar is my problem. (is there anyway dat i can change the format of the formula bar)
Attached Images
File Type: jpg Excel-1.jpg (9.0 KB, 2 views)
File Type: jpg Excel-2.jpg (14.7 KB, 2 views)
Reply With Quote
  #4  
Old 08-10-2011, 05:48 AM
Catalin.B Catalin.B is offline Date formatting Windows Vista Date formatting Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Sorry Tony, but no matter how you format the same date in different cells, when you compare these cells with a formula will say they are equal, except the case when one of the cells is formatted as text...
if this is the case here, the problem can be solved converting text in date, with : =DATE(RIGHT(L16;4);MID(L16;4;2); LEFT(L16;2)), where L16 is the cell containing date formatted as text; only now you can correctly compare dates ...
Reply With Quote
  #5  
Old 08-10-2011, 08:17 AM
kjxavier kjxavier is offline Date formatting Windows XP Date formatting Office 2007
Advanced Beginner
Date formatting
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default Date Formatting

Hi!

Is there anyway i can make the Formula bar display the same
as the fields A1, B1, C1, D1.(PLEASE REFER TO THE ATTACHED EXCEL FILE) i don mind how it is formatted.

E.G: when i click A1 the contents or values in A1 and the Formula bar should be the same. what ever the format or input may be.
Attached Files
File Type: xlsx PROB.xlsx (13.3 KB, 3 views)
Reply With Quote
  #6  
Old 08-10-2011, 10:59 PM
Catalin.B Catalin.B is offline Date formatting Windows Vista Date formatting Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

As i guessed, cell A1 was formatted as text, thats why looks different than other cells in formula bar, and excel cannot compare it with other cells containing dates, without the formula posted, to convert text as date. Appears that you formatted cell A1 as mm.dd.yyyy, but the format is not yet applied to the cell... instead of 1 click to cell A1, double click it to open it, then press enter, it will look now the same format as other cells in formula bar .. (Please observe that the value in A1 is allined to the left, as text does, and B1, C1, D1 to the right, as numbers does...) you have to decide: format ALL cells as text, or format ALL cells as date...then they will look the same in formula bar
Reply With Quote
  #7  
Old 08-12-2011, 05:46 AM
kjxavier kjxavier is offline Date formatting Windows XP Date formatting Office 2007
Advanced Beginner
Date formatting
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default Date Formatting

Quote:
Originally Posted by Catalin.B View Post
As i guessed, cell A1 was formatted as text, thats why looks different than other cells in formula bar, and excel cannot compare it with other cells containing dates, without the formula posted, to convert text as date. Appears that you formatted cell A1 as mm.dd.yyyy, but the format is not yet applied to the cell... instead of 1 click to cell A1, double click it to open it, then press enter, it will look now the same format as other cells in formula bar .. (Please observe that the value in A1 is allined to the left, as text does, and B1, C1, D1 to the right, as numbers does...) you have to decide: format ALL cells as text, or format ALL cells as date...then they will look the same in formula bar
thanks....

I double clicked (08/06/2005)the field and saw the date exactly the same as in the formula bar (8/6/2005)as you said. but when i pressed Enter key. it again returned back to the same format(08/06/2005).

is there anything i have missed in between.
Attached Files
File Type: xlsx PROB.xlsx (13.3 KB, 1 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date formatting Calculating age at date jamierbooth Excel 2 02-22-2011 03:35 AM
Date formatting Date Formatting in MS word 2007 Karthick Word 4 01-19-2010 05:54 AM
Date formatting Inserting Date, formatting to superscript and subscript louq Word 1 10-22-2009 09:29 AM
new appointment date always reverts back to today's date msills Outlook 0 08-24-2007 08:57 AM
Date formatting Imported message date change to today's date promark Outlook 1 12-23-2005 07:21 AM


All times are GMT -7. The time now is 09:32 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft