Search code examples
dategoogle-sheetsformattingstring-formattingnumber-formatting

Google sheets converts 01/01/0001 into 01/01/2001

How to make it stops? Tried many different formattings and it gives no difference. Could not find anything in settings. Googling only gives how to stop google sheets from auto-formatting numbers into dates, which is not the problem.

Solution

  • also you could use:

    ="01/01/0001"

    to calculate the age vertically try:

    =SUMPRODUCT(REGEXEXTRACT(B1:B2, "\d{4}")*{-1; 1})

    enter image description here

    or horizontally:

    =SUMPRODUCT(REGEXEXTRACT(A11:B11, "\d{4}")*{-1, 1})

    enter image description here