Date- change from serial number to normal format

[Manoharal]

I have been trying to type a date in a column in MS excel. But excel always reads it as a serial number. How Can I get it to display the date as it is in date format. I have the next column formatted/ formulated to display the day of the date in the previous column.

 

[Sal Paradise]

=TEXT(A1,"dd/mm/yyyy") or whatever you want your format to be, assuming the serial is in A1, or change the cell formatting to the preferred date format.

 

[Manoharal]

Thanks for the reply Sal. My problem is this- I need a column entirely dedicated for dates. The next column is formatted to show the day of that particular date. Now I have formatted the first column to show the date in a particular format say dd/mm/yy or mm/dd/yy (I tried several formats). But excel refuses to show the date and shows only the serial number. I have faced this problem several times before also. I don't remember how exactly I solved it at that time. What you have mentioned is a formula. I want to know how to make excel to show the date as date not as serial number. Hope I am clear in explaining my problem.

 

[Sal Paradise]

Are they numbers or text?

In some cell type =A1+1

If it gives a '#VALUE!' error then it means they are text.

In that case, in some unused column you can type =--(A1) and copy down, then paste special-values over your old data (and it should work).

 

[Manoharal]

I have copied the data from sheet1 to sheet2. In sheet 1 i used to enter e.g 3/1/2009 and excel returns the date to the cell as 1.3.09 as formatted by me. Now in sheet 2, when I do the same, it is returning as '39873', which is the serial number for 3.1.09. I wonder, why the copy function has not copied all the formatting, as it usually does in other cases. Secondly, why is excel giving a serial number instead of date, even after proper formatting under cell>format>number>date. Is there something else I have to do?

 

[Sal Paradise]

Ah -- what function are you using to copy? Could you paste the copy portion of your code to peruse?

 

[Manoharal]

Copy portion
Date Day

28.6.08 (fx 6/28/2008) Sat { =TEXT(WEEKDAY(H4), "DDD") }
5.7.08 Sat
12.7.08 Sat
19.7.08 Sat
20.7.08 Sun
26.7.08 Sat
30.8.08 Sat
3.9.08 Wed
13.9.08 Sat
2.10.08 Thu
4.10.08 Sat
9.10.08 Thu
11.10.08 Sat
18.10.08 Sat
25.10.08 Sat
1.11.08 Sat
13.11.08 Thu
15.11.08 Sat
22.11.08 Sat
30.11.08 Sun
27.12.08 Sat

paste portion

Date Day

39641 =TEXT(WEEKDAY(I4), "DDD")
39648 =TEXT(WEEKDAY(I5), "DDD")
39649 =TEXT(WEEKDAY(I6), "DDD")
39655 =TEXT(WEEKDAY(I7), "DDD")
39690 =TEXT(WEEKDAY(I8), "DDD")
39694 =TEXT(WEEKDAY(I9), "DDD")
39704 =TEXT(WEEKDAY(I10), "DDD")
39723 =TEXT(WEEKDAY(I11), "DDD")
39725 =TEXT(WEEKDAY(I12), "DDD")
39730 =TEXT(WEEKDAY(I13), "DDD")
39732 =TEXT(WEEKDAY(I14), "DDD")
39739 =TEXT(WEEKDAY(I15), "DDD")
39746 =TEXT(WEEKDAY(I16), "DDD")
39753 =TEXT(WEEKDAY(I17), "DDD")
39765 =TEXT(WEEKDAY(I18), "DDD")
39767 =TEXT(WEEKDAY(I19), "DDD")
39774 =TEXT(WEEKDAY(I20), "DDD")
39782 =TEXT(WEEKDAY(I21), "DDD")
39809 =TEXT(WEEKDAY(I22), "DDD")

 

[Sal Paradise]

Could you post a sample of your sheet using the HTML maker found in this post? I thought you were using VBA, but it looks like it's just formulas. Posting your data will help.

 

[Manoharal]

Thank you Sal. I think I was not clear in explaining my problem. Anyway, I found it myself at last. Here is the solution for the problem, for all those novices, like me, out there. While you work on excel worksheet, if you press the combo Ctl+` (key just below Esc key- also features ~) the sheet gets into an expanded mode and shows only the default values and actual formulas (instead of showing the values). In my case, as serial numbers are the default values for date formats in excel, the sheet was returning only the serial numbers instead of showing the actual date as per the format selected by me. I might have accidentally pressed the above key combination and started working on the sheet and ended up breaking my head to get the things right. It’s a toggle key combo- if you press the combo again the sheet returns to normal. After troubling all of you over the net, I started trying all Ctl+ combos and fortunately this worked. As simple as that!

 

[2DudesOC]

Don't be like me and mistake ` for a piece of dust! Had the same problem here...however, sadly at first I didn't read the full post. Since the tilde symbol is easier to see (~), might be easier to reference pressing the CTL and ~ keys together. As Manoharal states, its just below the ESC key. Easy solution to a frustrating problem.