Share this content
35

DD/MM/18 in Excel

Possible to do?

Didn't find your answer?

Something I've tried to do a few times but never can, is there a way to make an Excel cell format so if you type 01/04 it puts 01/04/18?  I tried setting the format as dd/mm/18 but that just confuses it.

Basically if I am typing a 100 dates, all in 2018, it seems daft to have to do three extra key strokes for each line.

Replies (35)

Please login or register to join the discussion.

avatar
By jaskew
20th Sep 2019 08:03

Not aware of a way to do it using a format, however I would use the EOMONTH function. Assume your date (say 01/04/19) is in cell A1, this function will give an output of 01/04/18:

=EOMONTH(A1,-13)+1

Thanks (0)
Replying to jaskew:
Psycho
By Wilson Philips
20th Sep 2019 09:17

But that doesn't work for dates other than the 1st of the month?

Thanks (0)
Replying to Wilson Philips:
avatar
By jaskew
20th Sep 2019 09:39

Good point (I hadn't had my coffee when I wrote my original response): this will work better in this scenario:

=DATE(2018,MONTH(A1),DAY(A1))

Thanks (3)
Replying to jaskew:
Psycho
By Wilson Philips
20th Sep 2019 09:48

As I said, there had to be a much more elegant solution than my clumsy suggestion :)

Thanks (0)
avatar
By spidersong
20th Sep 2019 08:57

I tend to do a spare cell with a value of 365 (or 366 for a leap year) and then Copy that and Paste Special with the subtract option.

Thanks (1)
avatar
By paul.benny
20th Sep 2019 09:03

In Excel, dates are a number (today is 43728) and it is formatting that represents that as 20/09/2019 or 20-Sep or whatever formatting you have chosen.

Do you actually mean 2018? If you don't specify a year, Excel assumes dates are in the current year. If you are trying to enter a lot of dates for a prior year, you do have to specify the year.

As a sort of shortcut, you could enter the dates as dd/mm - so 20/09 - which will default to 20-Sep. You may want to change the format to show the year. In an adjacent column enter a sum subtracting 365. That will automatically follow the date format of your first column. Then copy/paste special/values for the 2018 column and delete the 2019 one.

Thanks (0)
avatar
By WhichTyler
20th Sep 2019 09:15

You have just typed 320 additional characters to ask this...

Thanks (0)
Replying to WhichTyler:
Quack
By Constantly Confused
20th Sep 2019 11:45

WhichTyler wrote:

You have just typed 320 additional characters to ask this...

But I have more than 107 entries to make so I was hoping it would balance.

Thanks (2)
Psycho
By Wilson Philips
20th Sep 2019 09:16

It's clumsy, but you could set up a column to the side where you enter dd/mm as text in say G1. H1 has CONCATENATE(G1,"/18") In your date cell, say A1, DATEVALUE(H1) formattted as dd/mm/yy should give you the desired result. There must be a neater method.

Thanks (0)
Replying to Wilson Philips:
avatar
By paul.benny
20th Sep 2019 10:16

CONCATENATE doesn't work - it returns the date to its numeric value - so you end with a text field showing 43728/18.

Thanks (0)
Replying to paul.benny:
Psycho
By Wilson Philips
20th Sep 2019 10:33

It does work (and I've tested it) so long as dd/mm is entered as text.

But it doesn't really matter - there is a far better solution.

Thanks (0)
Replying to Wilson Philips:
avatar
By paul.benny
20th Sep 2019 11:02

Agree that it does work *if* the date is entered as text. By default Excel interprets 20/09 as being a date.

Thanks (0)
Replying to paul.benny:
Psycho
By Wilson Philips
20th Sep 2019 11:35

Yes, well, that is why I said in my first attempt at a solution that the date would need to be entered as text. It's not difficult - the cell range just needs to be formatted as text. By default Excel will then interpret 20/09 as being text.

Thanks (0)
Replying to Wilson Philips:
avatar
By maxmillion
19th Oct 2019 14:20

if A1 contains /18
Then B2 contains the date formatted as text eg, 09/10 (assuming all the dates are in Column B)
In C2 your formula would be =CONCATENATE(B2,$A$1)

Once you have completed inputting all the dates and copied the formula, then copy the info from column C and paste As back on itself. Thereafter delete column B.

Thanks (0)
avatar
By IslingtonAccountant
20th Sep 2019 09:22

Many years ago you were able to do this by changing your Windows system date to the year you desire, before starting Excel.

Thanks (1)
Replying to IslingtonAccountant:
Flag of the Soviet Union
By thevaliant
20th Sep 2019 13:35

I was going to suggest this.
Windows 10 might not let you do it anymore, but Windows 7 will let you change your date.
Just remember to disable Windows Time service whilst doing so, else it might update back to 2019.

Thanks (0)
avatar
By The Dullard
20th Sep 2019 11:07

Why not copy "/18" into your clipboard and then type your date "1/8" and then press Ctrl-V before you hit return.

Thanks (0)
Replying to The Dullard:
Psycho
By Wilson Philips
20th Sep 2019 11:31

With respect, there's a difference between laziness and efficiency.

Thanks (0)
Replying to The Dullard:
Quack
By Constantly Confused
20th Sep 2019 11:52

The Dullard wrote:

Why not copy "/18" into your clipboard and then type your date "1/8" and then press Ctrl-V before you hit return. Alternatively, just stop being so fuching lazy.

Without respect, shhh.

Thanks (0)
The triggle is a distant cousin of the squonk (pictured)
By Triggle
20th Sep 2019 11:51

Enter the date as just 15/12 in one column. Then, in another column enter =TEXT(A4,"DD/MM/")&"2018" where A4 is the cell that you have entered the date as 15/12. Drag down the formula for the other dates then, when you are done, copy and paste special>values into the original column.

Thanks (0)
Quack
By Constantly Confused
20th Sep 2019 11:51

Perhaps I wasn't clear in what I was aiming to do, or perhaps I just don't understand the responses :)

A client had sent me a looooong list of manual entries that needed moving to Excel. Rather than typing 06/04/18 07/04/18 07/04/18 08/04/18 08/04/18 08/04/18 08/04/18 for what must be around 500 lines I was hoping I could just type 08/04 and Excel would fill the rest in, making it much quicker and less likely I'd mis-key.

In this instance I solved the issue by emailing the client and finding out he had already transposed it to Excel but felt the book was what I wanted, not the electronic sortable filterable easily read version...

But I'd still be curious for going forwards. My best idea was to have a column of 06/04 style and a column with /18 and concatenate them, though I didn't get around to trying it yet.

Thanks (0)
Replying to Constantly Confused:
avatar
By daniel_
20th Sep 2019 12:33

The easiest way would be to have a column with just 06 (A1), a column with just 04 (B1) and make the third (C1) =DATE(2018,B1,A1).

If you need it to be specifically "06/04" in the cell (as Text), you could use =DATEVALUE(A1&"/2018") to concatenate the string and convert to a date number. This can then be formatted as a date.

Thanks (0)
Replying to Constantly Confused:
avatar
By agillies
26th Sep 2019 11:50

=DATE(2018,MONTH(A1),DAY(A1))
the solution provided earlier is simple will work with text 01/04 or date 01/04

you just need to copy the formulae down.

I had client tons of 01.04 format which was annoying but an extra column find replace . with / solved it

Thanks (0)
avatar
By Clinton Lee
20th Sep 2019 14:41

How about just letting Excel do its thing with 2019 and then, at the end, using a find and replace in that column?

/19 replace with /18

Disclaimer: I don't know what I'm talking about as I don't even use Excel, I'm just guessing here. I upgraded :) to LibreOffice Calc a long time ago.

Thanks (0)
Replying to Clinton:
avatar
By greenbunnygirl
26th Sep 2019 10:35

That's exactly what I would do - it works!

Thanks (0)
Replying to Clinton:
By Paul D Utherone
26th Sep 2019 13:39

Clinton wrote:

How about just letting Excel do its thing with 2019 and then, at the end, using a find and replace in that column?

/19 replace with /18

Disclaimer: I don't know what I'm talking about as I don't even use Excel, I'm just guessing here. I upgraded :) to LibreOffice Calc a long time ago.


You beat me to it. Just what I was going to suggest
Thanks (0)
avatar
By vic smith
26th Sep 2019 11:17

What I do in this situation is carry on and let all the dates default to 2019 and then do a find and replace to find all 2019's and replace with 2018..... ah just spotted the previous response who does this also

Thanks (0)
avatar
By David Gordon FCCA
26th Sep 2019 12:07

To find the number of cows in the field, count the legs and divide by four.
You do make things difficult:

Column A heading = yr2018
Format column A to round numbers or two decimal points as you prefer.
use USA date style:
so: 101 or 1.01= Jan 1st: 102 or 1.02= Jan 2nd and so on.
This has the advantage that one may log the bumff as it come off the pile, and then sort the list into date (i:e Numerical) order.
If you need to, number the bits of paper as they come off the pile and enter the number in Column B.
When you have finished put a treasury tag through the numbered pile.
This means you can find the item without first having to sort all the junk into date order.
Junior clerk stuff, really!

Thanks (0)
avatar
By MC1
26th Sep 2019 14:26

Can you not custom format as dd/mm/"2018"? ie with quotes around 2018.

Thanks (0)
avatar
By stanbu
27th Sep 2019 11:12

KISS
Have year in Col A against the first entry. Put all dates in Col B showing DD/MM. Change year in Col A when Col B goes into January.

Thanks (0)
avatar
By reconynge
30th Sep 2019 16:23

1/ For minimum keystrokes, with no further manipulation, provided you are happy with

* always entering three or four digits
* having a format of 01-04-18, say, instead of 01/04/18

then use the format 00-00-18. Thus 104 would become 01/04/18. However, 114 would become 01-14-18, hence the need for four digits, in this example 1104

2/ Alternatively, use the text format "0000"; then, in the adjacent column use a formula. Eg with A1 having 104 formated as text to show "0104", the formula should be

=IF(LEN(A1)=4,LEFT(A1,2)&"/"&RIGHT(A1,2),"0"&LEFT(A1,1)&RIGHT(A1,2)

whilst this formula could be streamlined, I haven't as that'll make it more complicated than it needs to be

Oh, depending on the version of Excel, the formatting as the formula would work for numbers

3/ A third alternative, but not one I would recommend, is - if you don't mind an extra keystroke and definitely want 01/04/18 - then use the format

dd/mm/"18"

BUT, this only changes the display of a 2019 date. Eg 1/4 would show as 01/04/18, but is actually 01/04/19

4/ Note those solutions I have seen which suggest then entering the dates then taking away 365 will cause problems if you apply them in the future when you have leap years to contend with as well as "normal" years

Thanks (0)
avatar
By paul.k2
10th Oct 2019 13:28

How about entering them with the current century, ie 10/10 will become 10/10/2019 and then just doing a find and replace.

I know its not elegant and only works for a common century, but it works.

Thanks (0)
avatar
By beanjuggler
18th Oct 2019 14:33

>Input so that it defaults to the current year /2019
> Use the EDIT / REPLACE function Replace /2019 with /2018

Hope I have understood the question

Thanks (0)
avatar
By emanresu
20th Oct 2019 15:22

Find and replace - as suggested.

or, if it wouldn't be easy to include all of your input in one Replace operation:

Set your PC clock back one year, type in your abbreviated data, then reset the clock...

Thanks (0)
avatar
By N.Krishnaswamy
22nd Oct 2019 13:11

Kswamy
First format the date column to have full year as 00/00/2019.
after typing dates up to December 31st, select the column and FIND ,Replace 2019 with 2018. From January it will be 2019 only. It will be hardly a minute's work.
If you put '18" only for the year and replace with '19'then dates '18' appearing in each month also will be replaced with '19'.

Thanks (0)
Share this content