# Date problem

#### texasalynn

##### Well-known Member
Well I thought I had this one fixed, but forgot about the end of year.

I have a date formatted like:
20041200
I use a formula:
=MID(K2,5,2)+1&"/"&"01"&"/"&LEFT(K2,4)
that gives me: 13/01/2004

which isn't what I expected. I needed 01/01/2005

Could someone please give me a formula that would give me that?

thanks . . . texasalynn

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
20041200 stands for what? And, what do you want to calculate from it?

20041200 stands for what? And, what do you want to calculate from it?

20041200 is a text field (an invoice period) that I need to calculate the next month.

I came up with
Code:
``=IF(MID(K2,5,2)<>"12",MID(K2,5,2)+1&"/"&"01"&"/"&LEFT(K2,4),"01"&"/"&"01"&"/"&LEFT(K2,4)+1)``

thanks . . . .texasalynn

texasalynn said:
20041200 stands for what? And, what do you want to calculate from it?

20041200 is a text field (an invoice period) that I need to calculate the next month.

I came up with
Code:
``=IF(MID(K2,5,2)<>"12",MID(K2,5,2)+1&"/"&"01"&"/"&LEFT(K2,4),"01"&"/"&"01"&"/"&LEFT(K2,4)+1)``

thanks . . . .texasalynn

But what does it mean - Year: 2004, Month:12, ...?

20041200

2004 = Year
12 = Month

texasalynn said:
20041200

2004 = Year
12 = Month

Would

=DATE(LEFT(K2,4),MID(K2,5,2)+1,1)

suffice?

no because as I said it is a text field and not a true date

Thanks . . . texasalynn

texasalynn said:
no because as I said it is a text field and not a true date

Thanks . . . texasalynn

DATE() coerces text-formatted numbers to real numbers implicitly. Just give it a try.

Replies
11
Views
1K
Replies
6
Views
407
Replies
9
Views
576
Replies
2
Views
501
Replies
3
Views
314

1,221,185
Messages
6,158,406
Members
451,490
Latest member
desktopace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back