+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting for time sheet

  1. #1
    Registered User
    Join Date
    10-31-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Conditional formatting for time sheet

    I need to conditional format a time sheet to fill in a colour from start time to end time. i.e the sheet is divided into columns showing every half hour ( B1 = 0030 , C1 = 0100, D1 = 0130, E1 = 0200 .. etc until 0000 hours)

    How I have rows for different activities unter Column A . I also have for eg. "Sipping" tea is in A3. If I put time I started Sippin tea as 0100 in Cell AX3 and time I completed Sippin Teas as 0200 in AY3. The how do I get the cells to get filled from 0100 until 0200 in the range of cells in my time sheet.

    I have attached the actual time sheet that requires the inputs.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Conditional formatting for time sheet

    Hi and welcome to the forum

    The file you uploaded doesnt exactly match with what you described (row 3 is completely empty?) Also perhaps if you showed us (manually?) what you want your answer to look like, it would help?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-31-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Conditional formatting for time sheet

    Hi thanks.. I will stop despairing for now
    Sorry I meant row 7

    I want the cells in row 7 to be conditional formatted to get highlighted depending on the values of AZ7 and BA7. So in this case i want the range of cells from k7 to p7 to get a different colour.
    I want the cells in row 7 to get highlighted for values between 0530(k4) and 0800(p4). So this means that the conditional formatting has to look up the values in row 4 based on the values in AZ7 and BA7 and then colour the range on cells in row 7 based on that. in the attachment provided I have manually coloured the cells , but i want conditional formating to do the same.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Conditional formatting for time sheet

    OK take a look at the attached. I made a slight adjustment to the value you had in BG11 (new value is in BF11 for comparison). Formula I ended up with was this...
    =AND(ROUNDDOWN(B$4,6)>=ROUNDDOWN(MOD($AZ7,1),6),ROUNDDOWN(B$4,6)<=ROUNDDOWN(MOD($BA7,1),6))

    Some of your times (row 4) are just times, but other of your times (AZ and BA) are actually dates AND times.
    AZ7 for instance is 41428.2291666667 (for 5:30 am) compared to the actual 5:30 time, which is 0.229166666666667 - so you can see how those 2 would never match just like they are

    Just FYI, excel treats times as a decimal of 1 day, so 06:00 am is actually 0.25, 12 midday is 0.5 and 06:00 PM (18:00) is actually 0.75

    So, take a look at the attached and let me know what you think?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-31-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Conditional formatting for time sheet

    Sweet !!! .. it works.. yes I figured that the time in row 4 were much smaller than the values in AZ7 and were not working.. just could not figure out how.

  6. #6
    Registered User
    Join Date
    10-31-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Conditional formatting for time sheet

    BTW... Thanks a GAzzillion !!!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Conditional formatting for time sheet

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

+ 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] Is there any way to time Conditional Formatting?
    By foxguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2013, 10:12 AM
  2. Replies: 9
    Last Post: 05-13-2013, 05:43 AM
  3. Conditional Formatting Time...
    By Will03 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2013, 10:09 PM
  4. [SOLVED] Conditional formatting with time
    By Sparkplug90 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-10-2013, 11:10 PM
  5. Replies: 4
    Last Post: 01-11-2012, 07:59 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