formula for adding a date
 

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

formula for adding a date
 

This worked for me:
=TEXT(DATEVALUE(SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")



Dslady wrote:

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

--

Dave Peterson

formula for adding a date
 

This shorter version worked, too:

=TEXT((SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")



Dave Peterson wrote:

This worked for me:
=TEXT(DATEVALUE(SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")

Dslady wrote:

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

--

Dave Peterson

--

Dave Peterson

formula for adding a date
 

I have tried both of these and can't seem to get them to work. On the short
version I keep getting a message that a value is of the wrong data type.

"Dave Peterson" wrote:

This shorter version worked, too:

=TEXT((SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")



Dave Peterson wrote:

This worked for me:
=TEXT(DATEVALUE(SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")

Dslady wrote:

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

--

Dave Peterson

--

Dave Peterson

formula for adding a date
 

If you type a formula like this into an empty cell:

=len(a1)
(change A1 to the cell's address that contains the yyyy mm)

What's returned?

If you type this formula that empty cell:
=code(mid(a1,5,1))
what do you see?

If you don't see 7 for the first formula and 32 for the second, then the value
in that cell isn't what I thought it would be--plain old text like 2007 08

Any correct formula will need to know what you really have in those cells.

Dslady wrote:

I have tried both of these and can't seem to get them to work. On the short
version I keep getting a message that a value is of the wrong data type.

"Dave Peterson" wrote:

This shorter version worked, too:

=TEXT((SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")



Dave Peterson wrote:

This worked for me:
=TEXT(DATEVALUE(SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")

Dslady wrote:

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

formula for adding a date
 

I think the problem is that we are using regular text but instead of being
2007 05 we are using 1875 05. I think excell is having trouble recognizing
1875 as a correct year.

"Dave Peterson" wrote:

If you type a formula like this into an empty cell:

=len(a1)
(change A1 to the cell's address that contains the yyyy mm)

What's returned?

If you type this formula that empty cell:
=code(mid(a1,5,1))
what do you see?

If you don't see 7 for the first formula and 32 for the second, then the value
in that cell isn't what I thought it would be--plain old text like 2007 08

Any correct formula will need to know what you really have in those cells.

Dslady wrote:

I have tried both of these and can't seem to get them to work. On the short
version I keep getting a message that a value is of the wrong data type.

"Dave Peterson" wrote:

This shorter version worked, too:

=TEXT((SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")



Dave Peterson wrote:

This worked for me:
=TEXT(DATEVALUE(SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")

Dslady wrote:

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

formula for adding a date
 

Excel likes years that start at 1900 (or 1904 depending on an option).

This seemed to work ok with "1875 01" through "1875 12"

=TEXT(LEFT(A1,4)+(RIGHT(A1,2)="12"),"0000")
&" "&IF(RIGHT(A1,2)="12","13",TEXT(RIGHT(A1,2)+1,"00" ))




Dslady wrote:

I think the problem is that we are using regular text but instead of being
2007 05 we are using 1875 05. I think excell is having trouble recognizing
1875 as a correct year.

"Dave Peterson" wrote:

If you type a formula like this into an empty cell:

=len(a1)
(change A1 to the cell's address that contains the yyyy mm)

What's returned?

If you type this formula that empty cell:
=code(mid(a1,5,1))
what do you see?

If you don't see 7 for the first formula and 32 for the second, then the value
in that cell isn't what I thought it would be--plain old text like 2007 08

Any correct formula will need to know what you really have in those cells.

Dslady wrote:

I have tried both of these and can't seem to get them to work. On the short
version I keep getting a message that a value is of the wrong data type.

"Dave Peterson" wrote:

This shorter version worked, too:

=TEXT((SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")



Dave Peterson wrote:

This worked for me:
=TEXT(DATEVALUE(SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")

Dslady wrote:

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

formula for adding a date
 

This works perfectly. I greatly appreciate it. We had 750 lines to update.

Thank you very much.

"Dslady" wrote:

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

formula for adding a date
 

Glad to hear it.

Dslady wrote:

This works perfectly. I greatly appreciate it. We had 750 lines to update.

Thank you very much.

"Dslady" wrote:

I have a four digit space 2 digit cell example (yyyy mm) and I need to ad one
to the month. Is there a way fo doing this?

--

Dave Peterson