Posts by monti

    I need help editing a formula that I got from another post from Dave Hawley - the code works perfectly for what I am looking for. However the formula I need is an average formula that converts the average to a time value. This is the formula that I use however, I need to keep changing it as my ranges change, so I want to add a vba code that will do this automatically
    [TABLE="width: 633"]

    [tr]


    [td]


    [TABLE="width: 633"]

    [tr]


    [td]

    =INT(AVERAGE($H$2:$H$29)) & "." & RIGHT("0" & ROUND(MOD(AVERAGE($H$2:$H$29)*60,60),0),2)


    How can I replace the above formula to the formula below "=Sum(" & strAddress & ")"

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [/TABLE]



    Appreciate any help or suggestion - Monti

    Re: Macro for Conditional Formatting - Excel 2007 based on Date/Time Value


    Hello - thanks for your help - no it does not have to be conditional formatting. Since the data changes every week, the entire sheet is replaced weekly with the new information. I need a way to highlight the cells that shows Arrival equal to or greater than 4 pm in any way possible. Conditional Formatting is just something that I am familiar with.


    Thanks again- Monti

    I have a worksheet whose information changes constantly. We run the report weekly to create KPI's. I need to highlight the cells in the Arrival Column for everything received from 4 pm onward. I was given the formula on another post by NBVC to add to the conditional formatting -

    Code
    MOD(A2,1)>TIME(16,0,0)

    - which works great. However, when I replace the data in my Master Worksheet, the conditional formatting formula changes and I have to keep manually changing it.


    I am preparing the worksheet to be worked by other individuals who are not familiar with Excel, so I want to create a button with a macro that will do everything that needs to be done to get the spreadsheet ready. I am attaching a sample worksheet of what I am looking for. I already have a macro that Fixes the dates as the system downloads it with a leading space. I now want to create a second macro (or edit the existing macro - Fix Dates) that will go to the Main Sheet containing the data and will highlight in yellow all the Cells that contain Time that equals or is greater than 4. I have looked at other threats but cannot figure out how to apply it to my scenario.


    Note that once a week, I have to delete the information on the Main Sheet and replace it with the current information. I never know the number of rows that I am going to have for any given week, but my columns are consistent.


    Any suggestions or help would be much appreciated. Thank you - Monti

    Re: Conditional Formatting on Cell with Date and Time


    Wow that was quick thank you so much - however, I am trying to format the cell that contains the value falling within the time frame - I tried your formula, but I get a True/False on the B column - see below - even on the one that was 4:00 pm - Thanks


    [TABLE="width: 271"]

    [tr]


    [td]

    Date Range

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]7/17/2012 9:00[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 12:10[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 12:10[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 12:10[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 16:30[/TD]
    [TD="align: center"]TRUE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 16:00[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 9:00[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [/TABLE]

    I have a range of cells that contain Date and Time values - I need to highlight those cells whose hours are equal to or greater than 4 pm. I have included a spreadsheet sample but essentially, Column A has the date range and regardless of the date I need to highlight anything greater than 4 PM. Any suggestions - Thanks Monti


    [TABLE="width: 76"]

    [tr]


    [TD="width: 101, bgcolor: transparent"]Date Range
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/17/2012 9:00
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/23/2012 12:10
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 12:10
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 12:10
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 16:30
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 16:00
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 9:00
    [/TD]

    [/tr]


    [/TABLE]

    Trying to find the difference in hours, between 2 dates/times columns which excludes weekends and holdidasy. I am attaching my spreadsheet with the formulas I am using - have tried several formulas from forumns but unable to get the results I am looking for. I need the results to be in nunbers so I can create a graph showing trend.


    Appreciate any suggestions - thanks Monti