+ Reply to Thread
Results 1 to 4 of 4
  1. #1
    Member
    Join Date
    Jun 2004
    Location
    south jersey, USA
    Posts
    103

    excel formula - months, days, hours, minutes between two dates with times

    i have two columns formatted as date cells with dates & times, read 12/1/05 12:00am. I want to find out calculate how many months, days, hours, and minutes are between the two.

    i broke up the date and times in their own columns. Right now im stuck trying to find out how much time will pass to the end of the day. I'm testing with 12:56pm. trying to find how much time will pass to 11:59pm. the formula i wrote looks like this,
    =TIME(HOUR(11),MINUTE(59),SECOND(59))-X4.
    Problem is that this is a negative number and excel won't display it.

  2. #2
    Ultimate Member zepper's Avatar
    Join Date
    Sep 2002
    Location
    Finger Lakes area
    Posts
    2,396

    Lightbulb

    You have to work with GMT (military, 24 hour time) not the am/pm for calculations to work properly. So midnite is 24:00:00, 11:59:59pm would be 23:59:59 - and noon would be 12:00:00. If necessary you can convert back to the am/pm system for the results cell.

    .bh.
    Last edited by zepper; June 9th, 2005 at 03:05 PM.
    "Our freedom depends on five boxes: soap, ballot, jury, witness; and, when all else fails, Ammo. " ?author?

  3. #3
    Member
    Join Date
    Jun 2004
    Location
    south jersey, USA
    Posts
    103
    i changed to military time and that part now works. Thank you.

    however, now im trying to find how many days are between the end date and the begin date + 1, cus we already calculated the time for the first day. I wrote the following formula.

    =((X5)-(X4))+((DAY(Y4)-(DAY(W4)+1))*24)+Z4

    we're focusing on this part right here ((DAY(Y4)-(DAY(W4)+1))*24).

    w4 is begin date, y4 is the end date. Problem is when i tell it to multiply, it multiplys the number of days times 24. So if this was 1 day, i get +24 days out. I want to get +24 hours out.

  4. #4
    Member
    Join Date
    Oct 2004
    Posts
    72
    i have a spreadsheet that is supposed to calculate time, but when a date like 1/1/2006 23:59 and 1/3/2006 00:01 is entered, it sums it up as 2 minutes instead of 24 hours and 2 minutes. ?

    how much does it cost to have a spreadsheet custom made?

Quick Reply Quick Reply

If you are already a member, please login above.

What is the color of the sky?

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. I need help with an excel formula
    By finad in forum Applications and Operating Systems
    Replies: 1
    Last Post: March 11th, 2004, 06:28 AM
  2. Need Help with Excel Formula
    By MrPurple in forum Applications and Operating Systems
    Replies: 5
    Last Post: October 27th, 2003, 01:11 PM
  3. Excel Formula
    By Dubbercody in forum Applications and Operating Systems
    Replies: 11
    Last Post: July 31st, 2003, 04:11 PM
  4. Excel formula
    By skuz in forum Applications and Operating Systems
    Replies: 16
    Last Post: March 18th, 2003, 03:44 PM
  5. 89 Hours, 37 Minutes of UD LOST, AGAIN
    By Richard Cranium in forum Distributed Computing
    Replies: 6
    Last Post: February 2nd, 2002, 08:34 AM

Posting Permissions

  • You may post new threads
  • You may post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Copyright 2014 All Enthusiast, Inc