Confused
sbweid wrote on December 31, 1969 19:00 EST
I understand the first part of this example, but the second half is confusing. The formula referred to includes cell A1, but cell A1 only has the day in it. It also refers to a number "122203", but the example has 221203 in cell A3. Can you explain more clearly what is being done here?
Thanks!
answer to confused and another comment
chanebaum wrote on December 31, 1969 19:00 EST
Answer to confused: the 22 is the day of the month, you could modify the formula not to need this entry by replacing "A1" with "day(today())".
Of note, the String formula will only work correctly if the date is entered as a 6 digit number - e.g. you would need to enter the 5th as 05... but this will present another problem to novice users, as the usual formating on excel cells will drop the 0 preceding a number. I would propose the following formula:
=IF(LEN(A3)=6,(LEFT(A3,2)&"/"&MID(A3,3,2)&"/"&RIGHT(A3,2)),(LEFT(A3,1)&"/"&MID(A3,2,2)&"/"&RIGHT(A3,2)))
Extra column
lewmac wrote on December 31, 1969 19:00 EST
This requires an extra column on a table. I guess it could be hidden for print but it would have to be done each time. The top formula worked OK for me but the bottom one gives me a message #VALUE. I have no trouble when I do the sections individually, such as =VALUE(MID(A8,4,2)) -- in row 8.
Error
lewmac wrote on December 31, 1969 19:00 EST
Re the #VALUE message above: I had the cell set at General instead of a date format!!!! It works OK.
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Real Estate Loopholes: Secrets of Successful Real Estate Investing
