+ Reply to Thread
Results 1 to 6 of 6

Determining Time and Time Across 24h Periods

  1. #1
    Registered User
    Join Date
    06-02-2014
    Location
    Oklahoma, USA
    MS-Off Ver
    2010
    Posts
    31

    Determining Time and Time Across 24h Periods

    I've determined I don't like working with time in Excel, LOL

    Ive googled, printed out how to's and searched here but nothing I have seen and tried seems to be working the way I need.

    My workbook contains imported data that is done so automatically via a VBA script. The amount of data can be from just a few rows to a couple hundred. One of those columns imported is a time stamp column that contains a date and a time (Column B). What I need is to determine the number of hours from the point of that time stamp to now in hours, minutes, seconds in Column F.

    I added something of a helper in Q1 that keeps and updates, via VBA, the current time each time the sheet is activated. I would like to base the calculation off that since it would result in everything else being updated, which is what I would like to do.

    The second part to this, and I may need to post in the VBA section for this and if so just let me know, is that since the number of rows will vary from one import to next, I would like to just fill Column F with whatever formula is needed there just down to the last used row. I am open to suggestions and ideas here. If all of this needs to be done with VBA then so be it as long as I can get hours, minutes, and seconds in Column F.

  2. #2
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: Determining Time and Time Across 24h Periods

    In Column F insert this formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Where B1 is the cell/Col = Time

    And format the Cell/Column [hh]:mm
    Rgd
    RT
    If my answer(s) helped you, please add me reputation by click on *

  3. #3
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: Determining Time and Time Across 24h Periods

    I can post a work in progress, but I'm afraid i've stumbled upon a dead end. I used an IF statement for calculation, but then I realized that Excel uses 01.01.1900 as a default date, so even if the cell says, for example, 13:45, it actually means 01.01.1900 13:45. So in this case a statement like IF 13:45>NOW() will always be FALSE, even if the time from NOW() is 13:44 or less.

    That being said, the formula in green does not require IF, because it doesn't work at this point. If the time from the original stamp is less than the time now, you can just SUM together the hours and grab the minutes and seconds too, if needed.

    If the time from the stamp is more than the time right now, you have to substract, but I cannot figure out how to calculate that.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-02-2014
    Location
    Oklahoma, USA
    MS-Off Ver
    2010
    Posts
    31

    Re: Determining Time and Time Across 24h Periods

    Quote Originally Posted by rajeshturaha View Post
    In Column F insert this formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Where B1 is the cell/Col = Time

    And format the Cell/Column [hh]:mm

    Thank you for your reply. I guess I was thinking it completely wrong as that is way simpler than where I was going.

  5. #5
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: Determining Time and Time Across 24h Periods

    @Bmouse
    I Think you will need to post your question in a new thread.
    I can post a work in progress, but I'm afraid i've stumbled upon a dead end. I used an IF statement for calculation, but then I realized that Excel uses 01.01.1900 as a default date, so even if the cell says, for example, 13:45, it actually means 01.01.1900 13:45. So in this case a statement like IF 13:45>NOW() will always be FALSE, even if the time from NOW() is 13:44 or less.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Now()-Today()
    will produce current time only
    Last edited by rajeshturaha; 11-19-2014 at 07:20 AM.

  6. #6
    Registered User
    Join Date
    06-02-2014
    Location
    Oklahoma, USA
    MS-Off Ver
    2010
    Posts
    31

    Re: Determining Time and Time Across 24h Periods

    Thank you bmouse.

+ 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. Replies: 2
    Last Post: 08-26-2014, 07:42 AM
  2. Calculate time by time periods splitting productivity
    By cgfourman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2014, 03:04 PM
  3. [SOLVED] Determining if a reference time is within a certain time range
    By kweaver in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2012, 03:07 PM
  4. Determining if date/time falls within spanned time
    By Bytor47 in forum Excel General
    Replies: 5
    Last Post: 09-15-2009, 01:55 PM
  5. time durations within time periods (greater than/less than?)
    By rosieb13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-08-2007, 10:38 AM

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