Forum Discussion

dannyboy123's avatar
dannyboy123
Copper Contributor
Jul 20, 2025

Date time format

Hi

 

I have the following data..

 

Time

datedate_time
00:00:1125/12/202525/12/2025 00:00:11
00:00:1825/12/202525/12/2025 00:00:18
00:00:2025/12/2025

25/12/2025 00:00:20

00:00:2125/12/202525/12/2025 00:00:21

The date_time column is formaulate using the following formula. =CONCATENATE(TEXT(B2, "dd/mm/yyyy"), " ", TEXT(A2,"hh:mm:ss"))

I've changed the date_time datatype to this..

But the cell still returns True when istext() is applied to it and I can't change the format to anything else related to date

 

If I type the same value in manaully, it works.

 

Would be very grateful if anyone can help?

 

Many thanks

excel

2 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Jul 22, 2025

    Dates are integer value as plain number and times are decimal value. So, you can sum them together to represent date/time value. As HansVogelaar​ mentioned in his answer that you can just sum them like =A1+B1 then apply desired date/time cell formats. In addition, you can sum and then can use TEXT() function after summing date and time. Try-

    =TEXT(A1+B1,"d/m/e hh:mm:ss")

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 21, 2025

    CONCATENATE returns a text value, regardless of the formatting of the cell with the formula.

    Change the formula to

    =A2+B2

    and format as dd/mm/yyyy hh:mm:ss

Resources