tip of the day is unclear

**1a2s3d4f** · 11-16-2006, 03:12 PM

hi everyone,
Today’s Tip

Quickly Typing Dates into Cells

Typing a large amount of data into cells can be tiring, especially if it includes a series of dates.

Type the day (serial number only) into cell A1 and add the following formula to insert the month and year into cell B1:

=DATE (YEAR(TODAY()), MONTH(TODAY()), A1)

Type a full number in the cell and change it to a date by using the following formula:

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/" &RIGHT(A1,2))

For example, type 122203, the result is 12/22/03.

i did try the tip of the day; however, it seem that excell is not reconize the equation. i was wondering that any one have the correct way of doing the tip would share.

thanks

**swatsp0p** · 11-16-2006, 04:49 PM

Did you format the cell with the formula as a DATE? If not, you will get a number as a result.

e.g. for Nov. 16, 2006 you would enter 111606 into cell A1, the formula will return 39037 unless (and until) you change the format of this cell to a Date.

Choose any of the date options or create a custom date in the Format Dialog box.

Good Luck

**1a2s3d4f** · 11-20-2006, 02:51 AM

Thank you for reply,

i did set the cell format, however, it still not work correctly. can you give me step by step how this tip work.

thank you

**Bryan Hessey** · 11-20-2006, 04:36 AM

Originally Posted by 1a2s3d4f

Thank you for reply,

i did set the cell format, however, it still not work correctly. can you give me step by step how this tip work.

thank you

Hi,

The 'tip' gives it's own step-by-step, utilizing columns A & B it allows you to enter a number into column A and selects the first, last and middle two characters from that to form a date.

However, unless you are entering a great number of dates then it is possible that the 'regular' method of just type the date (with the extra two '/' keystrokes) into the cell will work for you, and save you setting up the extra column and formula.

If it is (quote) "still not work correctly" for you then you need to specify more detail, as to what are you typing in, to which cell, the format of that cell and the format of the formula cell, and the formula should be posted to ensure that your problem can be resolved.

hth
---

**oldchippy** · 11-20-2006, 09:35 AM

Originally Posted by 1a2s3d4f

Thank you for reply,

i did set the cell format, however, it still not work correctly. can you give me step by step how this tip work.

thank you

Hi, did the first formula work for you? If you type say 23 in cell A1, then the formula

=DATE(YEAR(TODAY()),MONTH(TODAY()),A1)

in cell B1, did you get 23/11/06 if you did, in the next example shown, instead of puting in 122203, put in 221203, then the formula

=DATEVALUE(LEFT(A1)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))

you should now get 22/12/03 with the cell formatted to DATE. It's the USA/UK way of entering dates???

**iturnrocks** · 11-20-2006, 04:28 PM

Originally Posted by 1a2s3d4f

Type the day (serial number only) into cell A1 and add the following formula to insert the month and year into cell B1:

=DATE (YEAR(TODAY()), MONTH(TODAY()), A1)

The thing about this formula is that it will return the month and year of whatever your computers date is set to. So if you are entering other months and years it will not work.

If all the dates you are entering are the same month and year, this formula would work, just set your computers clock to that month and year.

Also make sure the number format in B1 is set to date

tip of the day is unclear

LinkBack

Thread Tools

Rate This Thread

Display

tip of the day is unclear

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions