Date problem

[texasalynn]

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

 

[Aladin Akyurek]

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

 

[texasalynn]

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

=IF(MID(K2,5,2)<>"12",MID(K2,5,2)+1&"/"&"01"&"/"&LEFT(K2,4),"01"&"/"&"01"&"/"&LEFT(K2,4)+1)

thanks . . . .texasalynn

 

[Aladin Akyurek]

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

=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, ...?

 

[texasalynn]

20041200

2004 = Year
12 = Month

 

[Aladin Akyurek]

20041200

2004 = Year
12 = Month

Would

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

suffice?

 

[texasalynn]

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

Thanks . . . texasalynn

 

[Aladin Akyurek]

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.