+ Reply to Thread
Results 1 to 10 of 10

Date & Time combine

  1. 03-04-2014, 01:14 PM #1
    thong127
    thong127 is offline
    Forum Contributor
    Join Date
    01-31-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    191

    Date & Time combine

    Hello Gurus,

    I have two columns B & C
    in B Column the content is a date (2/26/2014) and in my C column is time ( 4:17:47 PM)
    I'm trying to combine them in D Column with a formula: =B1 + C1. but it giving me a result #Value!.

    I know I did this before, but I don't know now why its giving me error.


    Any Help is appreciated.

    Thank you.
    Reply With QuoteRegister To Reply
  2. 03-04-2014, 01:15 PM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Date & Time combine

    Try: =B1 & C1

    You are getting the error because Excel can't sum the two values. What you are trying to do is concatenate them.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 03-04-2014, 01:17 PM #3
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Date & Time combine

    Are you sure that B1 is real date (number) or Text? Try ISNUMBER(B1) to check.
    If B1 is text, using DATEVALUE(B1) instead of B1 in your formula.
    Quang PT
    Reply With QuoteRegister To Reply
  4. 03-04-2014, 01:28 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,869

    Re: Date & Time combine

    Quote Originally Posted by AliGW View Post
    Try: =B1 & C1

    You are getting the error because Excel can't sum the two values. What you are trying to do is concatenate them.
    And even better would be: =B1&" "&C1
    Reply With QuoteRegister To Reply
  5. 03-04-2014, 01:30 PM #5
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Date & Time combine

    If I add the two together I get 2/26/2014 16:17
    If I point to those cells and add and format as # I get 41696.68
    did you check all your formatting?
    is that something you're looking for?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  6. 03-04-2014, 01:41 PM #6
    thong127
    thong127 is offline
    Forum Contributor
    Join Date
    01-31-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Date & Time combine

    Hi bebo021999 , I tried to check my date using your formula and it gave me a false, so I change it now into a date using the datevalue, how can I change my time (4:17:47 PM) into number?

    Thank you.
    Reply With QuoteRegister To Reply
  7. 03-04-2014, 01:43 PM #7
    thong127
    thong127 is offline
    Forum Contributor
    Join Date
    01-31-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Date & Time combine

    Hi bebo021999 ,

    I tried to check my date using your formula and it gave me a false, so I change it now into a date using the datevalue, how can I change my time (4:17:47 PM) into number?

    Thank you.
    Reply With QuoteRegister To Reply
  8. 03-04-2014, 01:44 PM #8
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Date & Time combine

    just format it as number. should return 0.68.
    Reply With QuoteRegister To Reply
  9. 03-04-2014, 02:02 PM #9
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Date & Time combine

    Quote Originally Posted by thong127 View Post
    how can I change my time (4:17:47 PM) into number?
    TIMEVALUE can do that.
    Reply With QuoteRegister To Reply
  10. 03-04-2014, 02:33 PM #10
    thong127
    thong127 is offline
    Forum Contributor
    Join Date
    01-31-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Date & Time combine

    Thank you so much for all your help.
    I really appreciate it.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Unable to combine Date and Time
    By sajeel in forum Excel General
    Replies: 3
    Last Post: 06-15-2010, 03:18 PM
  2. Combine 2 Date + Time Columns?
    By gkk2001@alltel. in forum Excel General
    Replies: 2
    Last Post: 03-26-2009, 02:49 AM
  3. Combine date and time
    By BN-CD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-22-2006, 11:31 AM
  4. Combine Date & Time Cells
    By Ron Lieber in forum Excel General
    Replies: 2
    Last Post: 01-31-2006, 04:15 PM
  5. combine date from one column with time from another
    By Phyllis Salyers in forum Excel General
    Replies: 1
    Last Post: 08-30-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1