Excel Date

  Lady Lara 23:01 19 Apr 05
Locked

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 05

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 05

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 05

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 05

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 05

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

  lotvic 23:37 19 Apr 05

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 05

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 05

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.

Regards,

Simsy

  Lady Lara 23:50 19 Apr 05

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 05

Simsy.

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!!

LL

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

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

iPhone 9 and beyond: 32 amazing future smartphone developments - graphene, supercapacitor…