Share via

How can I combine date and time from two separate cells?

Anonymous
2024-02-24T13:27:43+00:00

Hi all

I have a date in the format dd/mm/yyyy in a1 and a time (for example 1400) to be entered in b1; the problem is how excel handles date and time, and the users that will be using the spreadsheet!

The result will need to be in excel's standard date and time format for a cell, dd/mm/yyyy hh:mm, because I will be adding hours to this value in another calculation.

However, I need the user to be able to enter the time in the format hhmm using the 24 hour clock without having to use the colon; if they have to type a colon WWIII breaking out will be the least of our problems.

I've tried using various combinations of time/text/general/custom hh:mm/custom 00:00 for the time and =concentate(text(a1,"dd/mm/yyyy")," ",text(b1,"hh:mm")) for the output but can't get it right.

It seems to fall down because excel has to have the colon otherwise it can't use the input or uses it as a date.

Can anyone got any ideas?

Thanks in advance

JMS

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-02-24T13:52:09+00:00

    In C1:

    =A1+TIMEVALUE(TEXT(B1, "00:00"))

    Format C1 as dd/mm/yyyy hh:mm

    20+ people found this answer helpful.
    0 comments
  2. Anonymous
    2024-02-24T14:47:18+00:00

    Thanks, I appreciate the help

    1 person found this answer helpful.
    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-02-24T14:33:29+00:00

    TIMEVALUE converts a text string that looks like a time value to a "real" time value that can be used in calculations.

    Similarly, DATEVALUE converts a text string that looks like a date to a "real" date.

    Excel uses your computer's system settings to recognize dates and times.

    0 comments
  4. Anonymous
    2024-02-24T14:20:23+00:00

    That's brilliant, thanks once again HansV!

    So, briefly reading about TIMEVALUE, excel converts any of the recognised time formats into the format it needs to use for various other functions?

    0 comments