Logging sheet and date formula didn't work, and i believe it could be a bug in Excel

hi there. doing a ampersand (&) to combine any numbers or texts will result in the cell turning into a text. Excel recognises dates as integers & time as decimals. to verify, type in 1jan2013 in A1. and 12 pm in B1. Format both cells to General. you will see that A1 is 41275 (meaning 41,275th day from 1 Jan 1900) & B1 is 0.5 (1/2 a day).

hence, doing this formula below converts it into a text:
=Main!B1&"-01-01"

you can convert it back into a date by doing these alternatives:
=--(Main!B1&"-01-01")
=Main!B1&"-01-01"+0
=Main!B1&"-01-01"*1
=DATEVALUE(Main!B1&"-01-01")

but again, it's pretty risky as it's based on your computer region settings. a safer option is:
=DATE(Main!B1,1,1)

not sure what you want in cell A4, but SUM of a text is 0. it's 0 Jan 1900. the reason why your alternative works by adding and subtracting is same as me doing the double negations, +0, or *1. it will convert any numbers in text form into a number.

Hi,

This is not a bug but just basic excel functionality.
Excel basic function is to do math hence it will always try to handle any operation as a math operation.
As you have formatted your cell as text; excel can't handle the math because of the alphanumerical character of the cell.
There are two easy ways to have Excel treat the text as a numerical to do the math.

1. The first onbe is to convert the text with a function, called VALUE
2. The second one is by adding a number to the text, like you did. If you want to pursue this way, better to pick 0 to add instead of 1. As adding 1 means you need to subtract 1 to have the correct result.

benishiryo

the sum is to copy the info from the cell above.

is there a better way of doing it?

Thanks for your explanation. My fault, didn't know that this was the reason. Now , I l know.

how about just:
=A3

SUM is normally used to add a range of cells. so rather than doing =A1+A2+A3+A4+A5, you can use =SUM(A1:A5)
so even some of your calculation isn't necessary for SUM. for eg. =SUM(A4+1). =A4+1 is sufficient.

hope that answers your questions. do mark as "Solved" if it does. Joris in post #4 has also chipped in, so do spend some time to see how each contribution helps you.