Forum Discussion

NomanMetaIT's avatar
NomanMetaIT
Copper Contributor
Jan 31, 2024
Solved

Dates are showing as 5 digit number in formula

Hi,

 

I'm facing an issue with formula. I have a lot of dates in cells. I want to use them in formula using AND function. But when I enter the cell reference that contains the date, it converts date into five-digit number instead of the date format I used in cell. As, you can see the error in below example. How can I use the exact date format in formula as in the reference cell? 

 

 

  • NomanMetaIT 

    That is the way dates are stored in Excel (as a count of days since 01/01/1900).  To reproduce the date with the formatting you require, use the TEXT function

    = "Check Number:" & check & " was received on " & TEXT(date, "dd/mm/yyyy")

2 Replies

  • NomanMetaIT 

    That is the way dates are stored in Excel (as a count of days since 01/01/1900).  To reproduce the date with the formatting you require, use the TEXT function

    = "Check Number:" & check & " was received on " & TEXT(date, "dd/mm/yyyy")
    • NomanMetaIT's avatar
      NomanMetaIT
      Copper Contributor

      PeterBartholomew1 

      Thank you Peter for a quick response. I was trying to figure out this issue on google/YouTube. But no resolution was found. You've made my day and saved a lot of time for me.

Resources