# Posts by jronaldlee

• ## Calculation of Breach

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:

=B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>10,AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),0.5,0)

=B2

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).
+IF(A2=1,2/24,IF(A2=2,4/24,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.
+IF(AND(A2=3,

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.
OR(TEXT(B2,"H")*1>10,

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.
AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),

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.
0.5,0)

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.

• ## Unable to Paste > Special > Values?

Re: Unable to Paste &gt; Special &gt; 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.

• ## Calculation of Breach

Re: Calculation of Breach

For column C (this is C2, just copy down):
=B2+IF(A2=1,2/24,IF(A2=2,4/24,8/24))+IF(AND(A2=3,OR(TEXT(B2,"H")*1>10,AND(TEXT(B2,"H")*1=10,TEXT(B2,"M")*1>0))),0.5,0)

• ## find 3 matching criteria then copy entire row

Re: find 3 matching criteria then copy entire row

Glad I could help - you're welcome.

• ## Unable to Paste > Special > Values?

Re: Unable to Paste &gt; Special &gt; Values?

The name of the file we are copying from varies - the software that generates that Excel file does not give it a consistent name, so that part is done manually by the end user.

• ## find 3 matching criteria then copy entire row

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:

• ## find 3 matching criteria then copy entire row

Re: find 3 matching criteria then copy entire row

Something like this?

• ## Unable to Paste > Special > Values?

Re: Unable to Paste &gt; Special &gt; Values?

If it helps, we're using Excel 2010.

• ## Macro to insert formula around cell contents

Re: Macro to insert formula around cell contents

Code
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.

• ## Macro to insert formula around cell contents

Re: Macro to insert formula around cell contents

You don't need Visual Basic to do this.

For example, if A2 is "Number of Sales" and B2 is the value, put this in A2:

=IF(B2="","","Number of Sales")

• ## Stacked double y-axis graph

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.

• ## Unable to Paste > Special > Values?

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:

Code
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:

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?

• ## If statement in calculated field, pivot table

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.

Thanks!

• ## Excel crash when sending file as attachment.

Re: Excel crash when sending file as attachment.

bump

• ## Excel crash when sending file as attachment.

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?

• ## Disable Links message on open

Re: Disable Links message on open

Works like a charm - thanks.

• ## Employee Schedule Builder

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,

James

• ## Disable Links message on open

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.

• ## Import text from file in all subfolders

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.

• ## Import text from file in all subfolders

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: