Posts by jronaldlee

    Re: Calculation of Breach

    No problem. It helps to know that Excel treats dates and times as a decimal. Dates themselves are whole numbers. Yesterday's date is represented by 41172, today is 41173. To add time, you add a fraction of the day, so today at noon is 41173.5. Today at 11:32 and 45 seconds (in the morning) is 41173.48108... Knowing that, if you want to add a day to a value, you add 1. To add one hour to a time, add 1/24. To add two hours, that's 2/24. Twelve hours is 12/24 (or 0.5).

    You should probably update your formula to this:


    For this formula, we start with the leading value, or the time the ticket starts.

    Next, we add two, four, or eight hours, depending on the priority. If A2 = 1, add 2/24 (or two hours). If A2 = 2, add 4/24 (four hours). Otherwise, it must be priority three, so add eight hours (8/24).

    Last, if it's priority three, we need to add twelve hours, but only under certain circumstances. First, we check if it's priority three.

    Then, we check for the circumstances. We know priority three is only from 6 am to 6 pm, so we can do a test based on the hour of the day. If it comes in after ten in the morning, we have to add an extra twelve hours to our time. This is tricky, though. If it comes in exactly at ten am, there's no need to add half a day, but 10:01 does require the extra day. To solve for this, I use an OR statement.

    The first part of the OR checks to see if the hour is greater than ten. I do that by formatting the date/time value to just the hour. I used a text formula, which returns the text value of the number, so I multiply it by 1 to return it to a numeric value.

    The second part of the OR checks to see if the value in the hours position is a ten and the value in the minutes position is greater than zero. Because Excel uses the text format "M" to represent the month, I had to figure out another way to get the minutes into my logic statement. That's why I asked you to update the formula. I had to pull out the minutes and seconds together ("mm:ss"), then take just the left two digits to extract the minutes. The old formula is adding half a day if the hour is ten and the month is greater than zero. We want to add half a day if the hour is ten and the number of minutes is greater than zero.

    Finally, if priority is three, and either the hour is greater than ten, or the hour IS ten and the minutes are greater than zero, what do we add? Half a day. Otherwise, we add zero.

    There's a lot going on there, but I've been playing with time calculations and service levels in Excel for ten years plus, so I feel pretty comfortable with this (and am a little embarrassed that I messed up with the first formula I gave you). Sorry about that - but this will get you where you need to go. If any of this is confusing, let me know and I'll take another pass at explaining it.

    Re: Unable to Paste > Special > Values?

    This is solved. :wowee:

    In my office, Excel 2010 was installed with the Bluetooth Add In installed by default. I took it off her computer, and the values now copy properly.

    This doesn't make a lot of sense to me... I have other users who have the Bluetooth Add In, and no one else is experiencing the issue, but when I deactivated it, the macro started working properly. Posting my results in case someone else has a similar issue.

    Re: Calculation of Breach

    For column C (this is C2, just copy down):

    Re: find 3 matching criteria then copy entire row

    I forgot a line of code, sorry. This is copying the data to the same spot every time it finds a match... so it is looping through the whole thing, but it's putting it all in the same spot. Let's try one more bit of code:

    Re: find 3 matching criteria then copy entire row

    Something like this?

    Re: Macro to insert formula around cell contents

    1. Sub CovnertToFormula()
    2. For i = 1 To 1000
    3. Range("a1").Offset(i, 0).FormulaR1C1 = "=IF(RC[1]="""","""",""" & Range("a1").Offset(i, 0).Value & """)"
    4. Next i
    5. End Sub

    Edit: Assumes labels are in column A and values in column B. This would convert all of your labels to the IF statement in my original post.

    Re: Stacked double y-axis graph

    Your data isn't laid out in a way that is conducive to creating the graph you want. Make a table with four columns of data. 1a (actual), 1a (goal), 1b (actual), 1b (goal), then format the graph as needed.

    Each row of data should be one quarter for your graph. No gaps, no yearly roll up.

    We have software in our office that creates Excel reports as output. The reports are a mess. Data all over the place, no table format, merged cells everywhere, etc. To create usable data, I wrote a macro for end users to import the report daily. They open the report, copy columns A:V, then run the macro while those columns are in the clipboard.

    The macro has this line of code to Paste > Special > Values:

    1. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    This works for everyone that uses it but one person, who gets a run-time error 1004, PasteSpecial method of Range class failed. I tried to manually paste > special > values, and it is not available. The paste > special options are:

    Link to screen capture

    From what's available here, it looks like she's copying a picture. This feels like some sort of security option, but I can't figure it out. Any suggestions?

    Hi, all. I'm looking to make a calculated field in a pivot table that can be used to show the percent of scores that are 100 (the Score field can have values from 40 to 100).

    I thought this might work: =IF(Score = 100, 1, 0) - but no such luck.

    What I really want is to be able to make some buckets of my data. <98, 98 to <100, and 100 - then use the pivot table to display a pivot table & pivot chart to show the % of scores that are in the various buckets over time.


    I am using Excel and Outlook 2010. When I click File > Save & Send > Send Using E-mail > Send as Attachment, Excel crashes every time. The email is sent successfully, but I have to go through the whole recovery cycle to get back to Excel and get back to work.

    This is happening on a machine w/Windows XP.

    Has anyone experienced this, or know of a solution?

    Re: Employee Schedule Builder

    My advice would be to walk away from this idea. :stare:

    I have over a decade of professional scheduling experience, and would rather buy an off-the-shelf solution that try to code for all the complexities needed to do this right. Try googling "employee schedule software" and you'll find plenty of options, some with free trials.

    Good luck,


    I'm hoping there's a line of code I can add to the spreadsheet's "Private Sub Workbook_Open" macro to prevent an update link box I'm getting, which reads,

    "This workbook contains one or more links that cannot be updated. To change the source of links, or attempt to update values again, click Edit Links. To leave the links as is, click Continue."

    I've already set the links to update links automatically (found that in the "Edit Links" button), but it's still throwing this message, which I do not want users to see.

    Re: Import text from file in all subfolders

    I ended up with several bits of code to get where I wanted to go. First, I list the parent folders in column A, starting with A2. I use this code to create a list of subfolders to be examined in column C:

    Next, I have a list of text strings that the target file COULD have in column E (In other words, sometimes my files are named "PAUCV_1.txt", so I look for PAUC. Sometimes they're named "DSOP_1.txt", so I look for DSOP - I didn't include this in my original help request because I knew how to work through this issue). I cycle through each folder in the new list in column C. For each folder, I look at every file in that folder. For each file, I compare the name with every text string in column E. If it's a match, I record the name of the file in column F (these are the files I will be importing text from).

    Now that I have a list of files I want to examine, I ended up using another method for importing the text from these files:

    It's clunky, but it gets me where I need to go.

    Re: Import text from file in all subfolders

    Thanks for the second run at it. That's helpful.

    I'm still working on getting at "do that for every subfolder in the parent folder" - but with the info I found here:

    ...I think I'm going to be able to piece it all together. First I'll make a list of subfolders on one tab (output to a table instead of to a msgbox), then use that table to drive the importing of information from the code you helped with here. Once I piece it all together I'll post back here with the final macro - I'm sure someone else will like having it as a reference at some point!