# Date Converting To 1/0/1900 On Entry

Jan 30, 2008

I have a old spreadsheet file which I opened up in Excel 2003. It has a few worksheet tabs. 2 worksheet tabs seem identical. I will reference it as Worksheet1 and Worksheet2. In Worksheet1, I enter a date i.e. 1/25/2008 in any cell. It enters as inputted. But in Worksheet2, I enter the same date i.e. 1/25/2008, it appears as 1/0/1900. No matter what date I enter in Worksheet2 in any cell, it always appears as 1/0/1900. Now when I look at cell in the formula toolbar, it sees as a Math Formula meaning, it looks like this: =1/25/2008. How do I correct this Worksheet to recognize any date inputted, not to see it as a Math Formula?

## Converting 1900 Date System To Text

Jan 30, 2009

How do you convert a range of dates in this format: 39843 (i.e today 30th of Jan 2009). To text like: 20080130. Secondly, For the same date, how do you make the weeknum function give answer '05' and not just '5'

## Date Defaults To 01/00/1900

Aug 25, 2009

I am working with an Excel file that was created in 2001, has not been updated since.

In the first column on the first tab, is a date field, starting from 12/31/2000.

I try to change this to 12/31/2008, and I get the return date of 01/00/00. When I select the cell, the formula bar indicates 12/31/2008, but the cell shows 01/00/00.

If I change it back to 12/31/2000, it is back to normal. I tried to enter 2/3/2009 in another cell, converting to date format, and the same thing: 0/1/00 (Jan 0, 1900)

This is a US date format.

## Displaying A Date Of 00/01/1900 If The Value Is Zero

Feb 4, 2008

I currently have to display a large amount of data on a separate report. I have a master sheet which has columns and cells linked to another sheet, then a further sheet which has cells linked to the master sheet. The problem I am getting is when I link some of the date columns using a formula in the 3rd sheet, as this is looking at a cell that has another formula in on the other sheet it is displaying a date of 00/01/1900 if the value is zero, is there any easier way of getting the data to just display a blank as this is messing up the database it is being exported

## Date 1/0/1900 Displays Instead Of Blank

Feb 8, 2009

The result of my formula should be blank, but 1/1/1900 appears instead and I can't get rid of it. Does anyone know why or how to fix. I can't just change the font color as it screws up other formulas.

In this case the Vlookup is referring to an apparently blank cell with no formula in it. I googled 1/0/1900 and saw a few references that said formatting a cell with zero in it as a date would cause this result, but there is no zero in this case.

## Removing The 00/01/1900 Date When Looking Up Another Cell In Another Worksheet

Apr 12, 2009

I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2

## Produce Blank Cell If Date Is 00/01/1900

May 10, 2010

I have a column of data that requires the cell to be blank if the value is 00/01/1900. The data is linked to another cell. IE: cell E3 is linked to cell \$D\$3 The cell is formatted for dates in the format shown.

## Display 'Date' Cell As Blank Instead Of Default Year 1900

Nov 19, 2009

Three columns.

A - Date last checked
B - Due Date
C - Actual Date checked

Currently column B is formatted to Date and simply has =A+84 and will display a date 3 months in future. However if there is no date in column A, then column B displays a default 1900 date.. Is there a way of making this blank if there is no date in col A?

## Excel 2010 :: Converting Hijri Date To Gregorian Date From Another Cell

Apr 19, 2014

I have a spreadsheet where we will be inserting the Arabic Hijri date taken from a Customs document (bayan). On my spreadsheet I'd like to have a column immediately next to the Hijri date that would convert the Hijri date to the Gregorian date so both dates will be visible for our Arabic and Western personnel who will be using the spreadsheet.

I am using Microsoft Excel 2010.

## Converting Binary Date To Date / Time Format

Jun 9, 2014

I have a list of dates stored via the following :

20100101 how can I convert this to date/time format to show 01/01/2010?

## Military Date Format Needs Converting To Civilian Date Value

Jun 21, 2013

I have a program that exports the date as 20120621 (4 digit year, 2 digit month, 2 digit day). I have tried =datevalue(cell) but it returns #value!. I have tried =today()-(cell) and it also returns #value!. Is there a formula or something I can do to covert this field to a date?

## Converting Text To Date And Sorting On Date

Mar 10, 2014

I am trying to convert a long list of dates from text to date (see A) so that I can sort them from newest to oldest. I've tried converting them using differnt formulas (see B:C) but these don't work.

## How To Keep Date Entry TODAY Frozen On That Specific Date

Mar 6, 2014

I am working on a spreadsheet to create a school 'tracking' system based on excel. What I need is that as soon as a box in column B is ticked, a date (TODAY() is entered in column A. That is not so difficult, and I have used the formula in cells in column A:

=IF(ISBLANK(Bx);"";TODAY()), where x is the specific row.

So, keep cell A clear untill something is written in cell B. This works fine.

But, for the purpose I need to keep the TODAY() date fixed after entry. And it need to be erased again if the tickbox in cell B is emptied again. It need then to re-enter a new date after cell B is re-used.

## Restrict Date Entry To Be With Date Range/Scope

Jun 19, 2008

I'm having a problem with data validation. I set an entire column so that it could only be a date between the first date in the list and the current date. I tested a few cells to make sure that it was working the way I wanted and I noticed that it allows a random number like 3 or 5 to be input after row 50. This would create a date in the 1900s. Why is this happening and how can I stop it.

## Dates Before 1900

Sep 3, 2009

I'm an accountant, so I've always dealt with relatively current dates. Now I'm trying to do some geneology work, and I find that I can't put in a date prior to 1900. For example if I input "3/5/1870", it comes out as text. If I input "=date(1870,3,5)", it returns 3/5/3770 -adding 1900 years to my date. I don't need to do anything mathmatically with it, but it would be nice to have it return "March 5, 1870".

## Converting Date Getting #VALUE

Nov 14, 2008

I have an export from a database application in which I have thousands of dates. The dates are not in a date format (normal problem) so I wrote a formula to convert them as follows.

Original Date in A1 : 11/13/2008 This is mm/dd/yyyy

Formula : =VALUE(MID(A1,LEN(A1)-6,2)&"/"&LEFT(A1,LEN(A1)-8)&"/"&RIGHT(A1,4))

Result : 13/11/2008

This seems to work at first, until I looked more closely. If I change the date I am converting so that the date in cell A1 is 11/12/2008, ie. the dd value is less than 13, then the formula no longer works. It is fine for all values over 12.

All I get in these cases is #VALUE!

## Converting Date To Day

Jan 16, 2009

I am currently converting a date in column "A" to a day in Column "B"

Columns("B:B").NumberFormat = "dddd"
Range("B3:B" & lrow).Formula = "=A3"
can someone please advise what code I need to make the day stay as text only in column "B"
If I do a copy / paste special for column "B" and change it to text it reverts to the number of the date.
I would like it to stay as the day,eg, Monday, Tuesday, etc.

## Current Year In Cell Not 1900

Jan 15, 2009

I've conditioned formated some cells so that someone can type a number in and a date will appear in the cell.

Problem is if someone types 20 in the cell.. excel reverts back to Jan-20-1900

How can I get the current month and year when a number is typed in the cell?

Did excel exsist in the 1900?

SOLVED
Solution - I used the if command in A2 like this

## Stop 01/00/1900 Showing In Cell

Dec 20, 2006

I have a cell that has a formula in it and is also Excel Dates & Times coded. Hence when the repsective cells are empty and there is no values to be calculated the date "00-jan-00" shows. how to prevent this from showing up allowing for a blank cell to only show?

## Converting Numbers To Date

Jan 11, 2007

I have a HTML table with a column with numbers like

3.6
2.5
1.8
....

and so on...

When I'm trying to open this table in Excel (.csv or if I cut n paste from HTML) Excel keeps converting it to date format.

03.jun
02.may
01.aug

This wouldn't be a problem if I could only go to Format -> Cell and choose Standard or something.

The problem is it ends up like this then:

39236
39204
39295

WTF? :D

I want this!

3.6
2.5
1.8
....

## Converting Text To Date?

Sep 25, 2013

I have imported data that is showing dates as 18th Sep 2013. Excel does not like this and I have tried various methods of conversion but nothing seems to work. I will be getting data that has Oct, Nov, Dec etc so the formula needs to be able to handle these too. I would like these 'dates' converted to excel friendly dates.

## Converting Date Value To Day Of Year

Nov 14, 2008

I have several excel files with datevalues as the timestamp (column A). I would like to add 3 more columns after the timestamp and display the following (derived from the datevalue):
column B -> Day of Year
column C-> hh:mm
column D-> ss.0

how do I split an excel datevalue into Day of Year, hours and minutes, and seconds? Which formulas do I use?

## Converting Text To Date

Dec 18, 2007

I have few dates in column C. My Column B is blank. I want to convert all text in column C in proper date format in Column B.

The dates entered in column C is in this format 05.11.2007 (dd.mm.yy format) Is there any formula to convert it in proper dates in same which will show the date in 5-Nov-2007 format?

## Converting To Date Format

Aug 22, 2008

I collect data from a data historian and I'm having difficulty with a date format. The collected data is saved in a txt file and imported to Excel 2007.

The date returned by the historian is the following format

9/30/2007 8:45:18.000 PM

This format is not recongnized by Excel as a date format but as a text box.

How can I change this to a date format without having to change all the dates and times manually (over 10 000 dates)?

## Converting A Number To A Date

Aug 11, 2009

I have a column of numbers that are currently text, but they actually represent a date.

I had a column of dates, but needed to convert them into a "week number", so used this formula: =INT((A2-1)/7)

It produced a week number for each date, and produced results like 5594, 5595, etc.

I now need to "reverse" this, and take these numbers (5594, 5595...) and convert them back to actual dates.

## VB Code To Remove 00/01/1900 Dates From Spreadsheet

Feb 6, 2014

I have a spreadsheet which was formatted from VB code. Within the sheet there are four date columns where some dates show as 00/01/1900 (due to them having '0' in at the time of running the code). I am trying to run some code to blank out all cells showing the 00/01/1900 date, but the code just isn't touching it. I reverted to simply recording the 'find and replace' function and inserting into my code, but those irritating dates still remain.

Code I have used is as follows:

[Code] .....

## Converting Database Time / Date Into PST

Mar 28, 2014

I have a database spreadsheet where it shows, the date and time a sale was made in 2 different columns, one for date, one for time (in Epoch format). What I need to do is convert this date/time to PST vs. the EST it's defaulted to.

## Converting Text To Date / Time?

Apr 1, 2014

I am trying to convert text in a cell from: 03/08/2014 2330 to a date that looks like this: 03/08/2014 23:30. Then I also need to extract just the time only and put that in a new cell.

I can do it in a few steps by using Data -> Text To Columns, formatting the date and time separately, and then putting them back together again.

Is there a 1-step way to do the original text to date/time conversion?

## Converting A Day Date To A Month And Year

Oct 14, 2008

I am trying to use a pivot table to analyse some data. The base contains a "day" date..ie 01/05/08, however I want the pivot table to summarise by the month..ie May 08. I have used the "=month(cellref) command, and it returns the correct month number. However when I try and format this to "mmm" or "mmm yr" to get a month I can then cut and paste, it always retunrs the month of JAN.

## Converting Date Formats Depend On Value

Mar 10, 2009

I need to amend that column to display 26 Mar ( or 12 Dec or 17 Apr etc depending on value).

Try as I might, using format cells, nothing results in the required display. I'm not even sure what format the cells were in as received, since highlighting the received column and selecting "format cells" does not reveal the initial formatting. If I try pasting special to a new column and selecting values, I just get the numerical date value as a 5digit number.