+ Reply to Thread
Results 1 to 11 of 11

Fill a duration with one number

  1. #1
    Registered User
    Join Date
    12-26-2016
    Location
    Hong Kong
    MS-Off Ver
    Excel
    Posts
    32

    Fill a duration with one number

    Hi, This is a long shot, I don't even know if its possible. Can I type 9 (as in 9 hour shift) into the start hour and have the full 9 hour spaces fill.Or any number and fill the amount of spaces.

    Please find my example work sheet attached.

    Thank you in advance
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Fill a duration with one number

    This goes in the worksheet module:

    Please Login or Register  to view this content.
    For more information on worksheet events.
    https://www.excelforum.com/tips-and-...et-events.html

  3. #3
    Registered User
    Join Date
    12-26-2016
    Location
    Hong Kong
    MS-Off Ver
    Excel
    Posts
    32

    Re: Fill a duration with one number

    Hi daveexcel, many thanks for the reply, but you lost me. I inserted the code but no change. Sure some thing real simple but out of my depth there.

  4. #4
    Registered User
    Join Date
    05-21-2014
    Posts
    92

    Re: Fill a duration with one number

    You can also apply the following formula for Conditional Formatting:
    Please Login or Register  to view this content.
    Applied Range: $B$4:$GL$29

    NOTE:
    I don't think this will be a problem so long as shift times do not overlap, but if they do, the latest shift's end takes precedence over the end of a longer shift that started earlier.

    Example: If you were to input for Mike a 10 hour shift starting at 16:00 on Monday, and then immediately put in a 2 hour shift starting at 17:00 Monday, the yellow highlighting will only cover 16:00 (for 10 hour shift), 17:00 (for 2 hour shift), and 18:00 (for 2 hour shift). The 2 hour shift would cut short the 10 hour shift's end.

    Also, inputting non-numbers will result in an infinite hour shift.
    Last edited by Monimonika; 03-21-2017 at 02:34 PM. Reason: Added NOTE

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Fill a duration with one number

    Another way ...

    =COLUMN()-MATCH(10^10,$B4:B4,1)<=LOOKUP(10^10,$B4:B4)

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Fill a duration with one number

    AH,
    Please Login or Register  to view this content.




    The CF solutions work great

  7. #7
    Registered User
    Join Date
    05-21-2014
    Posts
    92

    Re: Fill a duration with one number

    Quote Originally Posted by davesexcel View Post
    The CF solutions work great
    Phuocam's CF solution works much better than my clumsy one. (No infinite hours with non-number inputs.)

  8. #8
    Registered User
    Join Date
    12-26-2016
    Location
    Hong Kong
    MS-Off Ver
    Excel
    Posts
    32

    Re: Fill a duration with one number

    Hi,
    Im really stupid at this. I've put in Phuocam's formula but im not getting the result.
    Can someone please look over it and see what im doing wrong.I type in 9 and at least 12 spaces fill then disappear.

    Much appreciated
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Fill a duration with one number

    See this ...
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-26-2016
    Location
    Hong Kong
    MS-Off Ver
    Excel
    Posts
    32

    Re: Fill a duration with one number

    Amazing, thank you so much...

    Can I be a real pain and ask it it is possible to have different colours depending on staff title just to make it more visible.And anything over 9 hours turns red. Sorry to be a pain but you make it look so easy.I have a lot to learn.

  11. #11
    Registered User
    Join Date
    05-21-2014
    Posts
    92

    Re: Fill a duration with one number

    Do you know how to apply Conditional Formatting (CF)? This should be easy to google, so I will not lay out the details here.

    I've added another column to the left of the staff names. This column would have the staff titles (I had just recently seen the Lego Batman Movie).

    The following are the formulas used in CF:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    For over 9 hour shifts:
    Please Login or Register  to view this content.
    The over 9 hours shift CF formula above needs to be the top-most CF rule for it to override the other CF rules.

    I had to modify Phuocam's formula to account for columns being added or shifted.

+ 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. Have start-stop times & duration, need sub-duration based on range criteria
    By CathTyner in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-23-2016, 01:53 AM
  2. Fill cell(s) with colour based on Duration/Time and Words
    By liqt in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-13-2015, 03:55 AM
  3. Replies: 3
    Last Post: 06-13-2015, 12:26 AM
  4. auto fill-in current date/time and duration
    By aturetsky in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-11-2013, 02:19 PM
  5. [SOLVED] Formula: To calculate duration between two times, if duration is over 6hours subtract 30mi
    By MattBarnes7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2013, 03:22 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1