Excel Date Time Concatination

Posted on 2014-08-12
Last Modified: 2014-08-12
Hi Experts,

In cell AW14 I have the following formula: =IF(AV14="","",AU14 & " " & AV14)

Where AU14 = 2014-08-01 (formatted as date)
and AV14 = 11:20:00 AM (formatted as time)

In AW14 I expect the formula to give me 2014-08-01 11:20:00 AM but it give me a number representation.

I tried formatting AW14 as text, but same thing. I also tried the following, but it gives me an error:

=IF(AV14="","",CStr(AU14 & " " & AV14))

Any help will be appreciated

Thank you
Question by:APD_Toronto
    LVL 25

    Accepted Solution

    Try this:
    =IF(AV14="","",AU14 + AV14)

    And set the format to a date/time format.

    The reason this works is that Excel treats dates as integers and time as decimals.

    Author Comment

    Strange, as soon as I change the formula as you suggested, the formula itself appears in the cell.
    LVL 25

    Expert Comment

    by:Shaun Kline
    I'm only aware of three things that can cause that (but there are probably more):
    1.) The equals sign was removed.
    2.) An apostrophe was added at the beginning of the formula.
    3.) The "Show formulas" button was clicked in the Formulas tab.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now