formula

[rellik666]

i have a requirement to make a spreadsheet for number manipulation, and struggling with the formula. knowing my luck at present its not possible!!

i need to take the date, in any format as i can state which on the sheet, iee ddmmyyyy or dd/mm/yyyy etc

change the numbers round to yyyymmdd and add 1 to each number.

is this possible?

thanx

James

 

[Yahya]

welcome to MrExcel Board
if you mean to 1 for the date , try this
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">25082011</td><td style="text-align: right;;">20110826</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=TEXT(<font color="Blue">A1+1,"yyyymmdd"</font>)</td></tr></tbody></table></td></tr></table><br />

 

[rellik666]

thanks for the speedy reply, however its +1 to all the numbers, so

20070101

becomes

31181212

 

[MikeWx]

What do you want for a result if adding one to each digit results in an invalid date, such as starting with 20071224? It looks like your result would be 31182335.

 

[Yahya]

thanks for the speedy reply, however its +1 to all the numbers, so

20070101

becomes

31181212

i don't what you want to do with 31181212
it is not date
but any way try this UDF

Function ADDONE(a As Range)
Dim i As Integer
For i = 1 To 8
    ADDONE = ADDONE & Mid(Range("a1"), i, 1) + 1
Next i
End Function

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">20070101</td><td style="text-align: right;;">31181212</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=ADDONE(<font color="Blue">A1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[rellik666]

thanks again, i just get #VALUE! in my b2

 

[rellik666]

What do you want for a result if adding one to each digit results in an invalid date, such as starting with 20071224? It looks like your result would be 31182335.

this is a back door code generator for a cctv company, it works off the date on the device

 

[rellik666]

spot on, thanks all

 

[rellik666]

thanks again for the earlier posts in this thread. now its come to september i've found an oversight!!

when a 9 is entered it needs to change to a 1.

at present 20110905 becomes

312211016

but needs to be 31221116

is this possible?