+ Reply to Thread
Results 1 to 3 of 3

Hours Formula Between 2 Dates

  1. #1
    Registered User
    Join Date
    02-27-2023
    Location
    London
    MS-Off Ver
    365
    Posts
    2

    Hours Formula Between 2 Dates

    I have an issue where I cannot create a successful formula between two date and time cells. The example shown below I would need to calculate A1-A2 however each time I get back a #VALUE! result.

    A1 - 2/22/2023, 10:10 AM
    A2 - 2/17/2023, 5:27 PM

    Any help would be greatly appreciated.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,451

    Re: Hours Formula Between 2 Dates

    Hi,

    You have US format dates, which excel reads as text. One way to resolve is as follows..

    1. Select column A
    2. Data > Text to columns > Delimited
    3. Select comma, space and forward slash as delimeters
    4. Next, Finish

    You should now have months in column A, days in column B, year in column C time in column D and AM/PM in column E

    In F1, enter

    =DATE(C1,A1,B1)+IF(E1="AM",D1,IF(E1="PM",D1+0.5,""))

    Then copy for all your data, and format as you wish it to be displayed.


    The difference in hours can then be calculated,

    =(F1-F2)*24
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    02-27-2023
    Location
    London
    MS-Off Ver
    365
    Posts
    2

    Re: Hours Formula Between 2 Dates

    Great thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Dates w/in 48 hours of each other formula/condition
    By JRose0303 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-05-2016, 01:28 PM
  2. Formula to work out total working hours between two dates
    By Lazyemployee123 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-27-2015, 07:40 AM
  3. [SOLVED] Formula to capture taken taken in hours between two dates and time
    By ed.mcardle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2015, 04:36 PM
  4. formula to get the HOURS from 2 dates with time
    By albert28 in forum Excel General
    Replies: 3
    Last Post: 08-01-2014, 02:35 PM
  5. [SOLVED] Formula to work out hours between 2 dates and times.. MINUS Out Of Hours
    By chris.m in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-30-2012, 08:11 AM
  6. Formula: Adding Hours to Dates
    By Grock258 in forum Excel General
    Replies: 6
    Last Post: 08-04-2008, 08:07 PM
  7. adding hours to dates using networkdays formula
    By melanie in forum Excel General
    Replies: 0
    Last Post: 02-17-2005, 01:35 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1