formula for adding a date

D

Dslady

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?
 
D

Dave Peterson

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

Dave Peterson

This shorter version worked, too:

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

Dslady

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 said:
This shorter version worked, too:

=TEXT((SUBSTITUTE(A1," ","/")&"/01")+31,"yyyy mm")
Click to expand...
 
D

Dave Peterson

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.
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.
Click to expand...
 
D

Dslady

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 said:
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.
Click to expand...
 
D

Dave Peterson

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"))



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.
Click to expand...
 
D

Dslady

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

Thank you very much.
 
Top