Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

How about you paste it in to PowerPoint as a table instead of an image? It will make it easier for other people to update to update slides.

# ? Aug 8, 2013 22:52

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 02:04

esquilax: Jan 3, 2003

It used to be the case that pasting excel tables in PPT would be awful and mess everything up. But apparently it solves my problems if I paste a table using "Keep Source Formatting" (Alt-h-v-k) which shows up under Paste Options and not the Paste Special menu. New PPT appears to be a lot better at dealing with Excel->PPT table pasting, so that's what I'm going with.

Thanks.

# ? Aug 9, 2013 00:31

Ron Don Volante: Dec 29, 2012

Is there a way to convert a date value, such as 31-Aug, into the day of the week ("Saturday")? The only way I can think of to do it is to create a lookup table with the named day for each date value, then use the DAY function and a lookup function to convert each date value into the day of the week. That seems way too complicated for something so simple, but Excel's date formats don't seem to include a day name format.

edit--Google's my friend; I'm a dumbass.

Ron Don Volante fucked around with this message at 03:11 on Aug 12, 2013

# ? Aug 12, 2013 03:08

Pegged Lamb: Nov 5, 2007; Can't post for 3 years!

I have a worksheet that has a formula:
=AND(VALUE($E$1)-VALUE(A4)>0,VALUE($E$1)-VALUE(A4)<=7)

$E$1 is todays date and column a is a vertical list of dates so I'm trying to find a particular date in that list that is 7 or less days ago but not today and apply conditional formatting to it. Problem is I have to do this for someone with a Mac and he's not seeing the formatting even though I can. I'm guessing it has something to do with the 1462 day difference in the two programs date systems (Mac uses a 1904, Windows 1900) but adding +1462 didn't seem to help.

# ? Aug 12, 2013 21:51

ZerodotJander: Dec 29, 2004; Chinaman, explain!

wikipe tama posted:

I have a worksheet that has a formula:
=AND(VALUE($E$1)-VALUE(A4)>0,VALUE($E$1)-VALUE(A4)<=7)

$E$1 is todays date and column a is a vertical list of dates so I'm trying to find a particular date in that list that is 7 or less days ago but not today and apply conditional formatting to it. Problem is I have to do this for someone with a Mac and he's not seeing the formatting even though I can. I'm guessing it has something to do with the 1462 day difference in the two programs date systems (Mac uses a 1904, Windows 1900) but adding +1462 didn't seem to help.

Not sure what is going wrong for you, but you can just apply 2 of the pre-built formats - highlight days within last 7, then format Today back to default.

# ? Aug 12, 2013 23:40

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

wikipe tama posted:

I have a worksheet that has a formula:
=AND(VALUE($E$1)-VALUE(A4)>0,VALUE($E$1)-VALUE(A4)<=7)

$E$1 is todays date and column a is a vertical list of dates so I'm trying to find a particular date in that list that is 7 or less days ago but not today and apply conditional formatting to it. Problem is I have to do this for someone with a Mac and he's not seeing the formatting even though I can. I'm guessing it has something to do with the 1462 day difference in the two programs date systems (Mac uses a 1904, Windows 1900) but adding +1462 didn't seem to help.

Zerodot has a better solution, but try replacing VALUE() with DATEVALUE().

# ? Aug 12, 2013 23:45

Pegged Lamb: Nov 5, 2007; Can't post for 3 years!

Huh it looks like datevalue did the trick

Pegged Lamb fucked around with this message at 01:11 on Aug 13, 2013

# ? Aug 12, 2013 23:48

melon cat: Jan 21, 2010; Nap Ghost

Is there an easy way to make Excel calculate and display ratios?

For example if A1 = 8, and A2 = 24, I want A3 to show 1:3. But I can't find any easy way to do this (or do it at all, for that matter...).

Suggestions?

EDIT: \/ Success! Thanks guys.

melon cat fucked around with this message at 04:30 on Aug 14, 2013

# ? Aug 13, 2013 03:07

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

melon cat posted:

Is there an easy way to make Excel calculate and display ratios?

For example if A1 = 8, and A2 = 24, I want A3 to show 1:3. But I can't find any easy way to do this (or do it at all, for that matter...).

Suggestions?

=SUBSTITUTE((TEXT(A1/B1,"?/?")),"/",":")

It takes the value and turns it into a fraction, then removes the / and puts in a :. Rusty as gently caress in Excel I need to frequent this thread more.

# ? Aug 13, 2013 03:27

esquilax: Jan 3, 2003

melon cat posted:

Is there an easy way to make Excel calculate and display ratios?

For example if A1 = 8, and A2 = 24, I want A3 to show 1:3. But I can't find any easy way to do this (or do it at all, for that matter...).

Suggestions?

You can calculate the ratio manually, and manipulate the text so that it shows up in that format. Essentially:

=A1/GCD(A1, A2) & ":" & A2/GCD(A1, A2)

# ? Aug 13, 2013 03:28

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

The issue with both of those is that your result is text, not a number. However, Excel will gladly parse it to a number if you use it as one...but it won't be what you expect: 3*"1:3" is 0.13125, not 1.

There is a number format that displays numbers as fractions ('#" "?/?' and similar, it's built-in), which will make arithmetic work correctly (well, it will display the fraction approximation up to the specified size, but use the exact number in computations). But I'm not aware of any way to make it work with ":" instead of "/".

# ? Aug 13, 2013 11:49

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

You could use Range.Value = Replace(Range.text, "/", ":") but then you'd lose the ability to do math with the values since they would have to be converted to text.

# ? Aug 15, 2013 21:33

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

ShimaTetsuo posted:

The issue with both of those is that your result is text, not a number. However, Excel will gladly parse it to a number if you use it as one...but it won't be what you expect: 3*"1:3" is 0.13125, not 1.

There is a number format that displays numbers as fractions ('#" "?/?' and similar, it's built-in), which will make arithmetic work correctly (well, it will display the fraction approximation up to the specified size, but use the exact number in computations). But I'm not aware of any way to make it work with ":" instead of "/".

I haven't tested it out, but how about '#" "?:?' as a custom number format?

EDIT: It doesn't work in Excel 2007.

Old James fucked around with this message at 01:02 on Aug 16, 2013

# ? Aug 16, 2013 00:59

fnkels: Aug 17, 2004

This has got to be the stupidest question I've ever asked, but I can't think of a better way to word it to Google the answer.

When creating a scatter plot of a large array of data, the plot always appears at the bottom of the array in Excel. For example, a 2 x 1500 array of data that I turn into a scatter plot, the plot appears around row 1500 (bottom of the array). To bring the plot to the top I have to click and drag the plot to the top. This is a slow and tedious process. How can I get the plot to the top of the spreadsheet quickly or am I just retarded with creating charts in Excel?

# ? Aug 16, 2013 22:48

ZerodotJander: Dec 29, 2004; Chinaman, explain!

You can cut and paste charts, it will paste so that the upper left hand corner starts in the upper left hand corner of the cell you have selected.

# ? Aug 16, 2013 22:53

fnkels: Aug 17, 2004

ZerodotJander posted:

You can cut and paste charts, it will paste so that the upper left hand corner starts in the upper left hand corner of the cell you have selected.

I am a moron. Thanks!

# ? Aug 16, 2013 22:54

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Old James posted:

I haven't tested it out, but how about '#" "?:?' as a custom number format?

EDIT: It doesn't work in Excel 2007.

Basically, the thing to do in cases like this is store the data on another worksheet or in hidden cells. Do math with the hidden data, and render it as text.

# ? Aug 20, 2013 22:07

gandlethorpe: Aug 16, 2008

Is it possible for an Excel VBA macro to interact with an external program, in this case a web browser? I have a macro that generates the javascript code, in Excel, for filling out a web form. I'd like for Excel to send that code directly to the browser and execute it.

# ? Aug 23, 2013 00:06

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

gandlethorpe posted:

I'd like for Excel to send that code directly to the browser and execute it.

What could possibly go wrong?

I just ripped this off some forum, haven't tested it because this is a terrible idea

code:

' ZVI:2011-05-30 VBA Macro For Already Open IE Window
' Reference required: Tools - References - Microsoft Internet Controls
Sub GetIE()
  
  Dim shellWins As ShellWindows
  Dim IE As InternetExplorer

  Set shellWins = New ShellWindows

  If shellWins.Count > 0 Then
    ' Get IE
    Set IE = shellWins.Item(0)
  Else
    ' Create IE
    Set IE = New InternetExplorer
    IE.Visible = True
  End If

  IE.Navigate "http://support.microsoft.com/kb/q176792/"

  Set shellWins = Nothing
  Set IE = Nothing

End Sub

# ? Aug 23, 2013 03:09

gandlethorpe: Aug 16, 2008

Can I ask what the danger is?

# ? Aug 23, 2013 15:49

Grawl: Aug 28, 2008; Do the D.A.N.C.E
1234, fight!
Stick to the B.E.A.T
Get ready to ignite
You were such a P.Y.T
Catching all the lights
Just easy as A.B.C
That's how we make it right

I have zero experience with Excel, but my company has given me the task to update an Excel file that calculates how much money each department has blown using our copyshop. For the most part it works fine, but as when the costs get too high (above a thousand Euros), Excel seems to freak out.

e: solved it, the width of the table wasn't big enough. :v:

Grawl fucked around with this message at 02:04 on Aug 29, 2013

# ? Aug 28, 2013 11:53

melon cat: Jan 21, 2010; Nap Ghost

I've run into an annoying issue with a spreadsheet I created a while back:

When I set up this spreadsheet, I made all of the cells below row #121 hidden (Ctrl + Shift + down arrow > Right click > Hide). But I've run out of space on this spreadsheet, and I want to unhide the cell Iv'e hidden from view. But when I right click > unhide, my hidden cells won't re-appear. And any new rows I add won't appear, either.

How do I fix this?

# ? Sep 3, 2013 20:24

ZerodotJander: Dec 29, 2004; Chinaman, explain!

The UI isn't very intuitive but what you need to do is click on the heading for the bottom row (the 121) and drag down, as if there were more rows available to select there. It'll give you the little tooltip that's like "1 million rows selected" and then you can unhide.

# ? Sep 3, 2013 20:52

melon cat: Jan 21, 2010; Nap Ghost

ZerodotJander posted:

The UI isn't very intuitive but what you need to do is click on the heading for the bottom row (the 121) and drag down, as if there were more rows available to select there. It'll give you the little tooltip that's like "1 million rows selected" and then you can unhide.

Yep. That did the trick. Thanks for the help!

# ? Sep 3, 2013 21:56

Fender Anarchist: May 20, 2009; Fender Anarchist

I'm using Excel 2010. Imported data from a text file, one of the columns is date and time listed as MM/DD/HHZ (as in Zulu time). I need to convert it to a recognized format for chart reasons. So for example, 08/13/15Z -> 08/13 15:00. Is there a way to do that?

# ? Sep 10, 2013 18:00

docbeard: Jul 19, 2011

Fucknag posted:

I'm using Excel 2010. Imported data from a text file, one of the columns is date and time listed as MM/DD/HHZ (as in Zulu time). I need to convert it to a recognized format for chart reasons. So for example, 08/13/15Z -> 08/13 15:00. Is there a way to do that?

Assuming that everything is going to be in MM/DD/HHZ format, and that the year is always going to be 2013, you could use the following (assuming your imported text value is in A1):

=DATE(2013,MID(A1,1,2),MID(A1,4,2)) + TIME(MID(A1,7,2),0,0)

This will convert it to Excel's numeric date/time notation, which you can then format as you please, use in charts, etc.

# ? Sep 10, 2013 18:10

Fender Anarchist: May 20, 2009; Fender Anarchist

docbeard posted:

Assuming that everything is going to be in MM/DD/HHZ format, and that the year is always going to be 2013, you could use the following (assuming your imported text value is in A1):

=DATE(2013,MID(A1,1,2),MID(A1,4,2)) + TIME(MID(A1,7,2),0,0)

This will convert it to Excel's numeric date/time notation, which you can then format as you please, use in charts, etc.

Looks like that did the trick, thanks!

# ? Sep 10, 2013 18:46

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Fucknag posted:

I'm using Excel 2010. Imported data from a text file, one of the columns is date and time listed as MM/DD/HHZ (as in Zulu time). I need to convert it to a recognized format for chart reasons. So for example, 08/13/15Z -> 08/13 15:00. Is there a way to do that?

In VBA, this is

code:

Selection.Value = Selection.Value

# ? Sep 11, 2013 21:00

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

I'm having a bit of a problem with VBA and date formatting and I cannot get the drat thing to work.

I'm pulling a date from a different piece of software in the format ddmmyy. So 1st March 2013 would be "010313". It comes as a string. I need it to be assigned to a cell in "dd-mm-yy" format.

Currently I'm splitting the input into "dd" "mm" "yy" and combining them DateSerial. However, no matter what I try to do VBA\Excel keep getting confused between England and American date formatting.

If the day of the date is <=12 it swaps it around so 1st of march would become 03/01/13 instead of 01/03/13.

This is more than just a display error however as it then, on later calculations assumes the date is in English formatting.

So, assume all dates are 1st March 2013.

Going in 01/03/13 (1st March 2013)
Passing to Cell 03/01/2013 (Americanised into 1st March 2013)
Used in later calculations 03/01/2013 (Sheet now thinks it is 3rd Jan 2013)

code:


'sDate has been tried as string and Date
'sInputYear etc are passed as "dd", "mm", "yy"

sDate = DateSerial(sInputYear, sInputMonth, sInputDay)

'Then

Worksheets("Worksheet").Range(sRange).Value = Format(sDate, "dd-mm-yyyy")

'Have tried with and without the formatting

Any ideas?

Checking in the locals window sDate does appear to be stored correctly, so the 1st of March would be stored as "#01/03/13#" it doesn't, however, pass this through correctly to the worksheet.

Also gently caress dates, gently caress dates forever.

Cast_No_Shadow fucked around with this message at 10:46 on Sep 16, 2013

# ? Sep 16, 2013 10:41

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Cast_No_Shadow posted:

Also gently caress dates, gently caress dates forever.

How are you getting the inputs? Datepart() or Left()/Mid()/Right()? If the first, you are leaving it up to Excel to assume which part is month and day, so try the second.

# ? Sep 16, 2013 13:04

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

I initially tried slicing the string as mentioned which didn't work. Latest try I've just pulled the two digit inputs separately rather than as a "ddmmyy" 6 digit string, which again doesn't seem to work.

Surely there is a way to actually get VBA to recognise that I want to explicitly define this number as the day and this number as the month. I'm telling you exactly how it is, stop making retarded guesses.

# ? Sep 16, 2013 13:19

Ragingsheep: Nov 7, 2009

I just tried it and its returning the correct behaviour. Maybe try WorksheetFunction.Text() instead?

# ? Sep 16, 2013 14:25

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Both of these are working for me using Excel2013

code:

Function StringToDateFunc(Value As Range) As Date
    Dim M As Integer
    Dim D As Integer
    Dim Y As Integer
    
    M = Mid(Value, 3, 2)
    D = Left(Value, 2)
    Y = Right(Value, 2)
    StringToDateFunc = DateSerial(Y, M, D)
End Function

Sub StringToDateSub()
    Dim M As Integer
    Dim D As Integer
    Dim Y As Integer
    
    M = Mid(ActiveCell.Offset(0, -1).Value, 3, 2)
    D = Left(ActiveCell.Offset(0, -1).Value, 2)
    Y = Right(ActiveCell.Offset(0, -1).Value, 2)

    ActiveCell.Value = DateSerial(Y, M, D)
    ActiveCell.NumberFormat = "dd-mm-yyyy"
    ActiveCell.Offset(1, 0).Select
End Sub

Edit: Yes, my input cells are in text format to force Excel to display the leading 0 on days 1-9.

# ? Sep 16, 2013 14:37

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Had a fiddle with your suggestions and still cannot get this to work. I also think I haven't explained myself properly.

I have a choice of inputs. All as string.

Either

"010313" (ddmmyy)

Or

A trio of

"01" (dd)
"03" (mm)
"13" (yy)

Which I'm pulling from a spaghetti mess of a Cobol system from the 70's.

I need to turn this into a date variable both VBA and excel understand. Yet nothing I try appears to work.

What should happen.

1) I take said input, format it into a date that VBA & excel understand.
2) Input this value into a cell.

What does happen.

1) I take said input, format it into a date. VBA seems to know what I mean.
2) I go to put this value into a cell. It is currently formatted dd-mm-yy.
3) When put into the cell it converts it to mm-dd-yy.
4) When later referencing this cell it thinks it is formatted dd-mm-yy but keeps the values as if it were mm-dd-yy.

I'll see if I can isolate it and pop up an example book. 2010 version if this matters. I generally know what I'm doing, so I feel stupidly retarded over this.

A1 should show 01/03/13 and understand this is dd/mm/yy.

Cast_No_Shadow fucked around with this message at 15:37 on Sep 16, 2013

# ? Sep 16, 2013 15:23

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Cast_No_Shadow posted:

What should happen.

1) I take said input, format it into a date that VBA & excel understand.
2) Input this value into a cell.

What does happen.

1) I take said input, format it into a date. VBA seems to know what I mean.
2) I go to put this value into a cell. It is currently formatted dd-mm-yy.
3) When put into the cell it converts it to mm-dd-yy.
4) When later referencing this cell it thinks it is formatted dd-mm-yy but keeps the values as if it were mm-dd-yy.

I'll see if I can isolate it and pop up an example book. 2010 version if this matters. I generally know what I'm doing, so I feel stupidly retarded over this.

Can we see the code you are using at step 4, when the cell is later referenced? Perhaps that code is what is messing up and not the initial conversion.

# ? Sep 16, 2013 15:27

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Old James posted:

Can we see the code you are using at step 4, when the cell is later referenced? Perhaps that code is what is messing up and not the initial conversion.

Popped an example up without any of the later stuff, same problem happening.

I'm probably doing something really dumb.

[Edit] Uploading that sheet seems to be all hosed. Basically

code:

Public Sub example()

Dim sDay As String, sMonth As String, sYear As String

sDay = "01"
sMonth = "03"
sYear = "85"

sDate = Format(DateSerial(sYear, sMonth, sYear), "DD-MM-YY")

Worksheets("sheet1").Range("A1").Value = sDate
End Sub

Cast_No_Shadow fucked around with this message at 15:37 on Sep 16, 2013

# ? Sep 16, 2013 15:30

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Cast_No_Shadow posted:

Popped an example up without any of the later stuff, same problem happening.

I'm probably doing something really dumb.

This is the only code I found in that example

code:

'Entry point for RibbonX button click
Sub ShowATPDialog(control As IRibbonControl)
    Application.Run ("fDialog")
End Sub

'Callback for RibbonX button label
Sub GetATPLabel(control As IRibbonControl, ByRef label)
    label = ThisWorkbook.Sheets("RES").Range("A10").Value
End Sub

The macro assigned to the button looks like it is referencing an external file which I don't have, FUNCRES.XLAM

Could you just post the code here or somewhere else? I don't like running xlsms I didn't code myself.

# ? Sep 16, 2013 15:37

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Sorry about that; I've popped the code above.

A1 needs to show dd-mm-yy. Cannot seem to stop it getting confused.

Cast_No_Shadow fucked around with this message at 15:44 on Sep 16, 2013

# ? Sep 16, 2013 15:39

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

This looks to be the problem

sDate = Format(DateSerial(sYear, sMonth, sYear), "DD-MM-YY")

change it to

sDate = Format(DateSerial(sYear, sMonth, sDay), "DD-MM-YY")

# ? Sep 16, 2013 15:46

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 02:04

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

This is not a VBA problem, it's an Excel problem. It automatically parses everything you put in a cell, which is often wrong. You cannot control this parsing from VBA. You may be able to prevent it from parsing incorrectly by writing it as an explicit date number or a string starting with a single quote (won't parse at all).

# ? Sep 16, 2013 15:47

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »