+ Reply to Thread
Results 1 to 6 of 6

Mark the Hours in use based on a start and finish time!!! HELP!!

  1. #1
    Registered User
    Join Date
    12-17-2013
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2013
    Posts
    10

    Mark the Hours in use based on a start and finish time!!! HELP!!

    I am working on an excel spreadsheet for work and I have to show a chart that shows the time of day a space is in use. Right now I am having to mark all of these by hand which takes me forever because I have a couple thousand lines of information.

    I need to mark the hours in use based off the start and finish time. For Example if the start time is 7:45 AM and the end time is 9:00 AM then I need a formula to mark the chart on the 7AM and 8AM spots. If the end time is 9AM I do not want that spot marked because it ends then it is NOT in use then.

    I was given the formula =IF(OR(AND($A2>=C$1,$A2<=C$1+30/24/60),AND($B2>=C$1+30/24/60,$B2<=D$1),AND($A2<C$1,$B2>D$1)),1,0) and it sort of works but it does not account for ALL of the 6AM hour or ALL of the 7AM hour...I need the formula to account for 7:00 - 7:59AM and 8:00 to 8:59AM...and I don't know how to fix it.

    Attached is an example of the spreadsheet

    Excel table.jpg


    Is this even possible?!
    Last edited by KattieSpencer; 12-17-2013 at 12:04 PM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,594

    Re: Mark the Hours in use based on a start and finish time!!! HELP!!

    Hi Kattie,

    I think part of the problem is that the formula you're using seems to be structured around the use of half-hourly booking periods, while your worksheet is structured around hourly booking periods.

    Take a look at the attached workbook and see if it does what you want.

    Hope this helps - please let me know how you get on with it.

    Regards,

    Greg M
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-17-2013
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2013
    Posts
    10

    Red face Re: Mark the Hours in use based on a start and finish time!!! HELP!!

    Thank you so much Greg M! This is exactly what I needed!!!

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,594

    Re: Mark the Hours in use based on a start and finish time!!! HELP!!

    Hi again Kattie,

    Many thanks for your very prompt feedback - I'm very pleased that I was able to help.

    Best regards,

    Greg M

  5. #5
    Registered User
    Join Date
    12-17-2013
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Mark the Hours in use based on a start and finish time!!! HELP!!

    Would you know how to do this on the half hours as well?

  6. #6
    Registered User
    Join Date
    12-17-2013
    Location
    San Antonio, TX
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Mark the Hours in use based on a start and finish time!!! HELP!!

    or quarterly ?

+ 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: 0
    Last Post: 11-17-2013, 10:21 AM
  2. [SOLVED] How do i calculate work hours from only a start and finish date and time?
    By transitsolutions in forum Excel General
    Replies: 1
    Last Post: 02-28-2013, 03:39 PM
  3. Calculate finish time given start time, working hours and job duration
    By swanseaexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2012, 03:00 PM
  4. Formulating Different Start/Finish Dates with Man Hours
    By dbaddorf in forum Excel General
    Replies: 5
    Last Post: 05-06-2008, 10:15 AM
  5. Replies: 1
    Last Post: 03-27-2006, 01:10 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