Excel Date

  Lady Lara 23:01 19 Apr 2005

Am using Excel 2003

If i type in 23.07.68 or 23/07/68 then that is exactly what i see in the cell. I am after seeing 23 Jul 68. I have tried with and without the 1904 option. I have tried ... format, cell and selecting date etc but it does not seem to work. I am trying a formula and dragging it down as my page consits of lots of dates but the formula does not carry down.

Help please

  Lady Lara 23:05 19 Apr 2005

I can put the formula in the first cell and add a date 1968,7,23 as suggested. hen i can drag the formula down the page but this populates each cell with the same date and i have to overtype each one.

  VoG II 23:07 19 Apr 2005

You definitely do not want the 1904 option.

Select the cells containing the dates, Format/Cells, select Custom and enter

dd mmm yy

  VoG II 23:10 19 Apr 2005

To copy the format down - click in the cell with the correct format. Click the Format Painter button (the paintbrush). Select the cells to format and release the mouse button. Voila!

  Lady Lara 23:18 19 Apr 2005

yeah.. VoG.. That works nicely. However I was hopint oenter a numerical value (quicker with dates etc) such as 23.07.98 which will return a 23 Jul 68. Is that way possible?

  Lady Lara 23:19 19 Apr 2005

23.07.98 should read of course 23.07.68 in the hope it will return 23 Jul 68

  lotvic 23:37 19 Apr 2005

Select first cell containing the date

>Format/Cells > select Custom and scroll down the list in the big box under the small 'type' box > select dd-mmm-yy

do as VoG™ said to copy format down the rest of cells

Then when you enter 23/7/68 it will return 23 Jul 68 in the cell just as you wanted it to

  lotvic 23:42 19 Apr 2005

and when you drag down the + it will enter the next day ie 24 Jul 68, 25 Jul 68 etc in the next cells

  Simsy 23:47 19 Apr 2005

Lady Lara is trying to work out a way of using a fullstop instead of a slash for ease of typing.

What to type in, and the formatting are two different issues.

If you format the cell "dd mmm yy" and then type in 23.7.68 Excel will see the fullstops and decide this is not a date and just treat it as text. (In the same way as if you format a cell to be a number with 2 decimal places and then type a word into the cell... it will assume the word is what you want in there and it will ignore the number format you have asked for and present the word)

If I understand the problem correctly, it can be done by using a macro to substitute what you actually type, for what you want typed.

I have a similar macro for time entry, (mostly not my own work!), and I'm sure I can ammend it to do this... but I'm not terribly quick at this, so can you confirm this is what you want before I get in to it.



  Lady Lara 23:50 19 Apr 2005

Thats just it

Have done format cells and chose Custom. Then i typed in 'Type:' box dd-mmm-yy and it has been added to the scrolling box. Then I clicked ok. Now If i enter 23 jul 68 it adds the - in between so it reads 23-jul-68 but when i type a numerical figure 23.07.68 or 23/07/68 it does not change it. If i type 230768 it adds a totally different date

  Lady Lara 23:53 19 Apr 2005


To type 23.07.68 and have it appear as a date 23 Jul 68 would be great? To be honest the (/) slash version is not playing either. I can get along the long and slow way but for the sake of number crunching dates it would save valuable time to crunch!!


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Illustrator of witty, relatable Instagram comics Julia Bernhard touches on our humble moments

iMac Pro review

Quelle est la meilleure application de podcast pour Android (2018) ?