Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for adding days to a date excluding weekends and holidays? | Excel Discussion (Misc queries) | |||
Adding years to a date | Excel Discussion (Misc queries) | |||
Excel Adding Date | Excel Discussion (Misc queries) | |||
Adding a date to calender to automatially generate another date? | Excel Worksheet Functions | |||
Adding a date and time | Excel Worksheet Functions |