Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

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

1. I have posted a date to a field as January 05, 2008 and formatted it to be 01/05/2008 (mm/dd/yyyy) and result was good. but when i select the field the formula bar shows 1/5/2008 instead of 01/05/2008 removing the preceding 0's



Problem is when i take this excel sheet to compare with another sheet with field where i have directly entered date as 01/05/2008.

Result is : 1/5/2008 and 01/05/2008 and it says both fields are different.

Is there anyway i can solve this prob. as i told you before the formula bar displayed the 1st date from the beginning rather that 01/05/2008

2. The same thing happens when i posted Januray 05, 2008 and formatted it as Jan-08(Mmm-yy) result OK. but formula bar still showed the same 1/5/2008
and the same happens again when compared with the one originally entered as Jan-08

ALL I WANT IS THE FORMAT TO BE THE WAY I FORMAT IT (CONSTANT) FOREVER
Reply With Quote
  #2  
Old 08-10-2011, 04:44 AM
Kimberly Kimberly is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

In Excel for Windows, a date is a serial number, where 1=Jan 1, 1900.

2=Jan 2, 1900; 29=Jan 29, 1900; 8/10/2011=40765, etc.

This is the reason you can do math with dates and the answer will be in number of days. Try it... put your next birthday in a A1. Put today's date in A2. Create a formula =A1-A2. the answer is how many days til your next birthday. (If the answer looks like a date, just change the format to number.)

No matter how you type a date in a cell, no matter how you format it, the formula bar will show it as 1/5/2008. If it doesn't, then Excel doesn't recognize that it is a date, and that is a big big problem.

Type in the date however you find easiest to type and then format it the way you like to see it.

By the way, times are just fractions of dates... Today's date is 40,765. Today at noon will be 40,765.5
Reply With Quote
  #3  
Old 08-10-2011, 05:29 AM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Sorry Kimberly, all you said is absolutely true, except this: " Create a formula =A1-A2. the answer is how many days til your next birthday."
The problem is that A1-A2 is a negative number, and the result is the age in days , and not how many days til your next birthday.

As for kjxavier problem, my guess is that the cell in which you have directly entered date as 01/05/2008 is formatted as text, and not as date, this is the reason excel says the dates are different.
You can solve the problem using in your comparrison formula to convert text to date:
=DATE(RIGHT(L16;4);MID(L16;4;2); LEFT(L16;2)) (-just replace L16 with referrence to other sheet) , and now, when you compare the date in this sheet with the result of the formula, will say they are the same...
Hope this solves your problem...
Reply With Quote
  #4  
Old 08-10-2011, 03:20 PM
Kimberly Kimberly is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Sorry Catalin, but A1-A2 is not a negative number. I have no idea why you think it would be.. A future date minus today will never return a negative number.

It's more serious than formatting. It's the wrong data type. Excel guesses at the data type... Text, Number/Date, or Boolean, based on the data entered. If entered incorrectly, the guess will be wrong. He can seperate it out this time using a formula simple enough, but without understanding the basics of the 1900 date system, dates will always be confusing and time-consuming.
Reply With Quote
  #5  
Old 08-10-2011, 10:04 PM
kjxavier kjxavier is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default Hello!

I never meant to do math with dates......... i just want them to appear the i want it........ please check with my last few threads

thanks
Reply With Quote
  #6  
Old 08-10-2011, 11:09 PM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Quote:
Originally Posted by kjxavier View Post
1. I have posted a date to a field as January 05, 2008 and formatted it to be 01/05/2008 (mm/dd/yyyy) and result was good. but when i select the field the formula bar shows 1/5/2008 instead of 01/05/2008 removing the preceding 0's

Problem is when i take this excel sheet to compare with another sheet with field where i have directly entered date as 01/05/2008.

Result is : 1/5/2008 and 01/05/2008 and it says both fields are different.

Is there anyway i can solve this prob. as i told you before the formula bar displayed the 1st date from the beginning rather that 01/05/2008

2. The same thing happens when i posted Januray 05, 2008 and formatted it as Jan-08(Mmm-yy) result OK. but formula bar still showed the same 1/5/2008
and the same happens again when compared with the one originally entered as Jan-08

ALL I WANT IS THE FORMAT TO BE THE WAY I FORMAT IT (CONSTANT) FOREVER
kjxavier, check your other thread:
http://www.msofficeforums.com/excel/...html#post21188 , but as Kimberly said, keep in mind that you cannot compare content entered as text to cells containing numbers/dates...

Well , Kimberly, you're right , should have read more carefully the post...(i read birthday instead of next birthday )
Reply With Quote
  #7  
Old 08-12-2011, 07:32 AM
kjxavier kjxavier is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default Date Formatting

Hi!

Here i have attached 2 excel files and comparison results of both in a software

they both appear to be the same.......

but in comparison it shows diff.... of-course the formatting is done differently but is there anyway i can make the comparison say that they are the same... even by changing the formatting

i appreciate you answer in a step by step procedure.

thank you
Attached Files
File Type: zip COMPARE.zip (34.8 KB, 1 views)
Reply With Quote
  #8  
Old 08-12-2011, 08:55 AM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

columns D and AD in sheet 1 are formatted as text. You should format these columns as date, and if necessary, reenter data, because in AD1, there is a text: "Nov-09" !! this is just a piece of text to excel, cannot recognize it as a date...There is no way excel can recognize it as 11/3/2009...( Nov can be recognized as month 11, but the day does not appear in that text ). in you file sheet1 reatached, i reformatted columns D and AD, and reentered 11/3/2009 in AD1. You should have no errors now..

tell me if you need further assistance in automatic reformatting and reentering existing data if you have a large number of rows...
Attached Files
File Type: zip COMPARE.zip (34.6 KB, 1 views)
Reply With Quote
  #9  
Old 08-12-2011, 10:29 AM
kjxavier kjxavier is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default Date Formatting

Quote:
Originally Posted by Catalin.B View Post
columns D and AD in sheet 1 are formatted as text. You should format these columns as date, and if necessary, reenter data, because in AD1, there is a text: "Nov-09" !! this is just a piece of text to excel, cannot recognize it as a date...There is no way excel can recognize it as 11/3/2009...( Nov can be recognized as month 11, but the day does not appear in that text ). in you file sheet1 reatached, i reformatted columns D and AD, and reentered 11/3/2009 in AD1. You should have no errors now..

tell me if you need further assistance in automatic reformatting and reentering existing data if you have a large number of rows...
That file looks fine
Thanks a lot for dat..

1. Now, how did you re format the field. (i really appreciate a step by step procedure from you) Becoz am not an expert in excel. I tried right click and format but it is not getting effected.


2. As you mentioned i have very large number of rows(4000). Reentering will be difficult task.

so, how do i do it.
Reply With Quote
  #10  
Old 08-12-2011, 12:02 PM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

check this link for instructions on how to set up your first macro...
http://office.microsoft.com/en-us/ex...138.aspx?CTT=1

repeat the operations in video, after you stop the macro, press visual basic button as picture attached, and replace your newly created macro with this macro: (your visual basic window should look like in picture attached...) Next step is to press the little green triangle highlighted in red in picture attached, it will do your job in less than 30 seconds. But attention, if you have wrong data in AD column, as i posted before, ( remember: "AD1, there is a text: "Nov-09" !! this is just a piece of text to excel, cannot recognize it as a date...There is no way excel can recognize it as 11/3/2009" )...you have to reenter data in those cells. Good luck !

Sub RecalculateCells()
Dim i As Integer
Application.ScreenUpdating = False

For i = 1 To 1000 'if you need more than 1000 rows, change here
ActiveSheet.Cells(i, 4).Select '4 means column D
Selection.NumberFormat = "mm/dd/yyyy" 'choose format for D column
ActiveCell.Value = ActiveCell.Value ' recalculate cells selected
Next i

For i = 1 To 1000 'if you need more than 1000 rows, change here
ActiveSheet.Cells(i, 30).Select '30 means column AD
Selection.NumberFormat = "mm/dd/yyyy" 'choose format for D column
ActiveCell.Value = ActiveCell.Value ' recalculate cells selected
Next i

ActiveSheet.Cells(1, 4).Activate
Application.ScreenUpdating = True
End Sub
Reply With Quote
  #11  
Old 08-12-2011, 12:07 PM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

forgot to attach picture
Attached Images
File Type: jpg Run macro.jpg (99.0 KB, 2 views)
Reply With Quote
  #12  
Old 08-12-2011, 07:32 PM
kjxavier kjxavier is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default

Quote:
Originally Posted by Catalin.B View Post
check this link for instructions on how to set up your first macro...
http://office.microsoft.com/en-us/ex...138.aspx?CTT=1

repeat the operations in video, after you stop the macro, press visual basic button as picture attached, and replace your newly created macro with this macro: (your visual basic window should look like in picture attached...) Next step is to press the little green triangle highlighted in red in picture attached, it will do your job in less than 30 seconds. But attention, if you have wrong data in AD column, as i posted before, ( remember: "AD1, there is a text: "Nov-09" !! this is just a piece of text to excel, cannot recognize it as a date...There is no way excel can recognize it as 11/3/2009" )...you have to reenter data in those cells. Good luck !

Sub RecalculateCells()
Dim i As Integer
Application.ScreenUpdating = False

For i = 1 To 1000 'if you need more than 1000 rows, change here
ActiveSheet.Cells(i, 4).Select '4 means column D
Selection.NumberFormat = "mm/dd/yyyy" 'choose format for D column
ActiveCell.Value = ActiveCell.Value ' recalculate cells selected
Next i

For i = 1 To 1000 'if you need more than 1000 rows, change here
ActiveSheet.Cells(i, 30).Select '30 means column AD
Selection.NumberFormat = "mm/dd/yyyy" 'choose format for D column
ActiveCell.Value = ActiveCell.Value ' recalculate cells selected
Next i

ActiveSheet.Cells(1, 4).Activate
Application.ScreenUpdating = True
End Sub

Hi!

Actually am formatting a date for example:

November 05, 2009 to Nov-09

not vise versa or

Nov-09 to 11/05/2009

Anyways letme check with the directions from you and get back...

Thank you Catalin
Reply With Quote
  #13  
Old 08-14-2011, 05:25 AM
kjxavier kjxavier is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default

Hi! Catalin

I have attached the files.

In that Field AD of file named 'Typed version' Its manually typed as : Nov-09 and the formula bar also displays the same(Nov-09). Its formatted as Text.

In that Field AD of file named 'Converted version' Its posted from MS word(copy paste): November 03, 2009 and formatted as Mmm/yy and the formula bar displays as 11/3/2009. Its formatted as Text.

What i need is the AD field of file 'converted version' to display the same i.e. Jan-09 (Mmm-09). and the formula bar should also display the same. (Its not possible to re-write its since they are tooo large in numbers)but I dont mind what the formatting is. sothat when i take both these files to Comparison Software Both fields will be the same and it will return No Error.

I have attached a comparison file result were the dates are same but it shows error just because both are in diff format.

Please refer to the AD field of Comparison file.

Please provide me with a good remedy.....
Attached Files
File Type: zip Compare1.zip (34.8 KB, 2 views)
Reply With Quote
  #14  
Old 08-14-2011, 05:29 AM
kjxavier kjxavier is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default

Quote:
Originally Posted by kjxavier View Post
Hi! Catalin

I have attached the files.

In that Field AD of file named 'Typed version' Its manually typed as : Nov-09 and the formula bar also displays the same(Nov-09). Its formatted as Text.

In that Field AD of file named 'Converted version' Its posted from MS word(copy paste): November 03, 2009 and formatted as Mmm/yy and the formula bar displays as 11/3/2009. Its formatted as Text.

What i need is the AD field of file 'converted version' to display the same i.e. Jan-09 (Mmm-09). and the formula bar should also display the same. (Its not possible to re-write its since they are tooo large in numbers)but I dont mind what the formatting is. sothat when i take both these files to Comparison Software Both fields will be the same and it will return No Error.

I have attached a comparison file result were the dates are same but it shows error just because both are in diff format.

Please refer to the AD field of Comparison file.

Please provide me with a good remedy.....
Sorry i mentioned Jan-09 for Nov-09.............sorry dont misunderstand
Reply With Quote
  #15  
Old 08-14-2011, 06:39 AM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

since the AD column in the sheet "typed " cannot be formatted to compare it to a date, the only thing i can advice you to do is to change column AD data in which date is entered correctly, to a piece of text that can be compared to the text in typed sheet: in an empty column, say AF, enter this formula: =TEXT(AD1;"mmm-yy"); copy this formula down to your row range; then click on column letter AF to select the entire column AF , click on AD1, and paste only values( paste special- only values). In this moment, AD1 column can be compared... look at sample attached..
Hope this fits you
Attached Files
File Type: xlsx Converted version.xlsx (33.9 KB, 1 views)
Reply With Quote
Reply

Tags
formatting

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date formatting kjxavier Excel 6 08-12-2011 05:46 AM
Date Formatting in MS word 2007 Karthick Word 4 01-19-2010 05:54 AM
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
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 08:56 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft