Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Turning numbers into Dates
I would like to know how to turn a number such as "51319" into a date such as
"5/13/19". As of now, when I try to format the cell to a date format, it completely changes the date from "52309" to a random date such as "7/11/35". I have no idea why. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Turning numbers into Dates
Hi,
Custom format and enter 0"/"00"/"00 "Kbass" wrote: I would like to know how to turn a number such as "51319" into a date such as "5/13/19". As of now, when I try to format the cell to a date format, it completely changes the date from "52309" to a random date such as "7/11/35". I have no idea why. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Turning numbers into Dates
That's OK providing that you just want to display it that way, and not
calculate as dates. -- David Biddulph "Eduardo" wrote in message ... Hi, Custom format and enter 0"/"00"/"00 "Kbass" wrote: I would like to know how to turn a number such as "51319" into a date such as "5/13/19". As of now, when I try to format the cell to a date format, it completely changes the date from "52309" to a random date such as "7/11/35". I have no idea why. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Turning numbers into Dates
That doesn't change the underlying value of the cell. It's not a date it's
still the same number just with the slashes. turn a number such as "51319" into a date such as "5/13/19". Ok, so what year is that? Is it 1919 or 2019? Try this... Select the cell in question Goto the menu DataText to Columns Click Next twice In Step 3 of the wizard, in Column data format, select Date and select MDY from the frop down. Click Finish Excel will interpret the year 19 as 2019 Then you can custom format as desired. -- Biff Microsoft Excel MVP "Eduardo" wrote in message ... Hi, Custom format and enter 0"/"00"/"00 "Kbass" wrote: I would like to know how to turn a number such as "51319" into a date such as "5/13/19". As of now, when I try to format the cell to a date format, it completely changes the date from "52309" to a random date such as "7/11/35". I have no idea why. Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Turning numbers into Dates
Changing the format of the cell doesn't change its value, but only the
method in which the value is displayed. 52309 is 52309 days from 0 Jan 1900, so is 19 March 2043. If you want to change the number into a date, try =--TEXT(A20,"00\/00\/00") and format the result as a date. That will work, providing that your windows regional options use the same convention as you have used for short date, which looks like m/dd/yy in your case. -- David Biddulph "Kbass" wrote in message ... I would like to know how to turn a number such as "51319" into a date such as "5/13/19". As of now, when I try to format the cell to a date format, it completely changes the date from "52309" to a random date such as "7/11/35". I have no idea why. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel is turning entered dates into formulas despite format. Why? | Excel Discussion (Misc queries) | |||
how to stop numbers turning to hash ? | Excel Discussion (Misc queries) | |||
Turning positive numbers to negative | Excel Discussion (Misc queries) | |||
Numbers turning to Zero's | Excel Discussion (Misc queries) | |||
How do I Turning Dates into Quarters | Excel Worksheet Functions |