Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Excel Date Time Concatination

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
0
APD_Toronto
Asked:
APD_Toronto
  • 2
1 Solution
 
Shaun KlineLead Software EngineerCommented:
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.
0
 
APD_TorontoAuthor Commented:
Strange, as soon as I change the formula as you suggested, the formula itself appears in the cell.
0
 
Shaun KlineLead Software EngineerCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now