Roger’s Excel Thread - Page 4 - Antsmarching.org Forums - Dave Matthews Band Discussion
Old 10-20-2016, 11:39 AM   #91
BTBaboon
 
BTBaboon's Avatar
 
Join Date: Jul 2015
Posts: 51,323

Shows Seen: 31

DMB Hub Stubs: 10

My Tour Central Stats

Re: Excel Help

Quote:
Originally Posted by FromTheMorning View Post
Please help me convert this into a standard short date:

Column A: numeric month (e.g. 12)
Column B: year (2016)

There is no day but I just have to make them all the 1st of the month. So in the example above I'm trying to get that to read 12/1/2016
In column 'C' type '=Date(B1,A1,1)'

Where B1 is your year and A1 is the numeric month.

Edit: Bridge beat me to it

Last edited by BTBaboon; 10-20-2016 at 11:40 AM.
BTBaboon is offline   Reply With Quote

  • Want to hide all ads on Ants? Click here
  • Old 10-20-2016, 11:43 AM   #92
    FromTheMorning
     
    FromTheMorning's Avatar
     
    Join Date: Dec 2011
    Posts: 12,752

    Shows Seen: 29

    DMB Hub Stubs: 13

    My Tour Central Stats

    Re: Excel Help

    Jesus fuck that was easy. I was trying to concatenate shit. Thanks
    FromTheMorning is offline   Reply With Quote
    Old 10-20-2016, 11:47 AM   #93
    thebridge15
     
    thebridge15's Avatar
     
    Join Date: Jun 2008
    Posts: 75,098

    Shows Seen: 20

    DMB Hub Stubs: 8

    My Tour Central Stats

    Re: Excel Help

    you could probably concatenate it, but I'm not sure if it would then read the result as a date if you're trying to do further analysis
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    bills are paying the jags o-line coach 4 million this year.

    that will be a tough one for the aliens to figure out.
    thebridge15 is offline   Reply With Quote
    Old 10-20-2016, 11:47 AM   #94
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by FromTheMorning View Post
    Jesus fuck that was easy. I was trying to concatenate shit. Thanks


    that would've been my first guess too. Learned something new!
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 10-20-2016, 12:33 PM   #95
    kydmb99
    Jorts
     
    kydmb99's Avatar
     
    Join Date: Oct 2005
    Location: Big Blue Nation
    Posts: 33,522

    Shows Seen: 10

    DMB Hub Stubs: 8

    My Tour Central Stats

    Re: Excel Help

    Not exactly excel related but does anyone here use SQL?
    kydmb99 is offline   Reply With Quote
    Old 10-20-2016, 12:35 PM   #96
    mr. testaverde
    King of Prussia
     
    Join Date: Aug 2003
    Location: location location
    Posts: 15,576

    Shows Seen: 58

    DMB Hub Stubs: 25

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by kydmb99 View Post
    Not exactly excel related but does anyone here use SQL?
    on a weekly basis, but by no means am i an expert
    __________________
    Mmm boy that Hennigans goes down smooth. And afterwards you don't even smell.
    mr. testaverde is offline   Reply With Quote
    Old 10-20-2016, 12:36 PM   #97
    kydmb99
    Jorts
     
    kydmb99's Avatar
     
    Join Date: Oct 2005
    Location: Big Blue Nation
    Posts: 33,522

    Shows Seen: 10

    DMB Hub Stubs: 8

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by mr. testaverde View Post
    on a weekly basis, but by no means am i an expert
    Yeah it's an awesome program but gets pretty complicated quickly
    kydmb99 is offline   Reply With Quote
    Old 10-20-2016, 12:37 PM   #98
    jmc419
     
    jmc419's Avatar
     
    Join Date: May 2016
    Location: Oh
    Posts: 5,814

    Shows Seen: 12

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Manage three servers and have since SQL2000.
    jmc419 is offline   Reply With Quote
    Old 02-07-2017, 12:21 PM   #99
    diableri
    Stay Limber
     
    diableri's Avatar
     
    Join Date: Sep 2004
    Location: West Coast
    Posts: 4,252

    Shows Seen: 4

    DMB Hub Stubs: 5

    My Tour Central Stats

    Re: Excel Help

    So I need a timeline of events displayed along a horizontal line with elapsed time hash marks.

    Something along the lines below. It's for individual illustrations on a case by case basis.

    Anyone have any tips to get there? I've blown about an hour digging around it's definitely not intuitive. I don't have trouble with the elapsed time, I am having trouble getting a graph to look like that.

    http://imgur.com/wEoHmp3
    __________________
    Quote:
    Never argue with stupid people, they will drag you down to their level and then beat you with experience.
    Mark Twain
    diableri is offline   Reply With Quote
    Old 02-07-2017, 12:29 PM   #100
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by diableri View Post
    So I need a timeline of events displayed along a horizontal line with elapsed time hash marks.

    Something along the lines below. It's for individual illustrations on a case by case basis.

    Anyone have any tips to get there? I've blown about an hour digging around it's definitely not intuitive. I don't have trouble with the elapsed time, I am having trouble getting a graph to look like that.

    http://imgur.com/wEoHmp3
    What do you mean "I don't have trouble with the elapsed time". What are you plotting on the vertical access?

    Like a gantt chart?

    https://www.smartsheet.com/blog/gantt-chart-excel

    Last edited by rconverse; 02-07-2017 at 12:32 PM.
    rconverse is offline   Reply With Quote
    Old 02-07-2017, 01:06 PM   #101
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Something like this?
    Attached Images
    File Type: jpg diableri.jpg (27.6 KB, 20 views)
    Attached Files
    File Type: zip diableri.zip (10.2 KB, 2 views)
    rconverse is offline   Reply With Quote
    Old 02-07-2017, 09:27 PM   #102
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    I just noticed this part of your post...
    Quote:
    It's for individual illustrations on a case by case basis.
    If that format works for you, I can make it more dynamic. It's static now.
    rconverse is offline   Reply With Quote
    Old 02-07-2017, 11:09 PM   #103
    diableri
    Stay Limber
     
    diableri's Avatar
     
    Join Date: Sep 2004
    Location: West Coast
    Posts: 4,252

    Shows Seen: 4

    DMB Hub Stubs: 5

    My Tour Central Stats

    Re: Excel Help

    Sorry I didn't get back to you earlier Rog. Thank your for the help. I'll give this a look in the morning and see if I figure it out from advice.

    We have a monthly meeting where case vignettes are discussed and they make these, by hand, every month for a couple dozen patients. The data elements are in a database (it's essentially a table of Event 1 @ 02/07/2017 15:34, Event 2 @ 02/07/2017 15:39 etc). Some patients have 5 Events, some have 7 or 9 etc depending on the treatment given that individual case). The important part is the various elapsed times between events in the course of the patient care.

    I'm used to looking at the table and pulling out the outliers or just using quick filters on raw numbers, committee is used to the timeline with hash marks and elapsed times. Try as I might, I was unable to make any headway on convincing them that we could just use the actual numbers and parameters, they have to see it. The admin assistant has retired and the new one basically begged for help not making versions of that red and green whiteboard drawing attached in my first post. I didn't think to put it in a gantt as I was focused on duplicating what they've been used to, that's a great suggestion; thank you.

    Thanks for taking a look at it at all, I appreciate the advice.
    __________________
    Quote:
    Never argue with stupid people, they will drag you down to their level and then beat you with experience.
    Mark Twain
    diableri is offline   Reply With Quote
    Old 02-08-2017, 03:20 PM   #104
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Anytime. If you need any additional help or advice, let me know. If you're using an Access DB, the whole thing could easily be automated.
    rconverse is offline   Reply With Quote
    Old 02-08-2017, 07:48 PM   #105
    diableri
    Stay Limber
     
    diableri's Avatar
     
    Join Date: Sep 2004
    Location: West Coast
    Posts: 4,252

    Shows Seen: 4

    DMB Hub Stubs: 5

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Anytime. If you need any additional help or advice, let me know. If you're using an Access DB, the whole thing could easily be automated.
    I set up a gantt for them. Hopefully everyone will be fine with that but there's some older physicians that really hate change in any form so I'll know next month. I just felt really bad for the gal when she realized she was going to hand type a bunch of labels in every month just for this meeting.

    There's something just infuriating about not using data when it's already in a table. Pet peeve. Thanks again.
    __________________
    Quote:
    Never argue with stupid people, they will drag you down to their level and then beat you with experience.
    Mark Twain
    diableri is offline   Reply With Quote
    Old 02-08-2017, 07:54 PM   #106
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by diableri View Post
    I set up a gantt for them. Hopefully everyone will be fine with that but there's some older physicians that really hate change in any form so I'll know next month. I just felt really bad for the gal when she realized she was going to hand type a bunch of labels in every month just for this meeting.

    There's something just infuriating about not using data when it's already in a table. Pet peeve. Thanks again.
    Huge pet peeve of mine as well. You're welcome. Let me know if I can provide any additional assistance.
    rconverse is offline   Reply With Quote
    Old 02-08-2017, 09:10 PM   #107
    DaveHead36
    YNWA
     
    DaveHead36's Avatar
     
    Join Date: Jul 2003
    Location: Texas
    Posts: 6,692

    Shows Seen: 4

    DMB Hub Stubs: 4

    My Tour Central Stats

    Re: Excel Help

    Been looking to get more familiar with excel. Are there any good tutorials or guides that are worth looking into? I can do basic things like sum and whatnot, not much beyond that, looking to gain a little more knowledge.

    Not really needing a specific area either, just want to be well rounded and quicker than I am currently.
    __________________
    Time flies like an arrow, fruit flies like a banana
    DaveHead36 is offline   Reply With Quote
    Old 02-08-2017, 09:15 PM   #108
    justink
     
    justink's Avatar
     
    Join Date: Jan 2002
    Location: Weir Everywhere
    Posts: 35,081

    Shows Seen: 51

    DMB Hub Stubs: 16

    My Tour Central Stats

    Re: Excel Help

    anybody have any good budgeting sheets? utilities, groceries, entertainment, eating out... etc?
    justink is offline   Reply With Quote
    Old 02-09-2017, 05:37 AM   #109
    DavefaninNZ
     
    DavefaninNZ's Avatar
     
    Join Date: Apr 2006
    Location: Colorado
    Posts: 4,777

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by DaveHead36 View Post
    Been looking to get more familiar with excel. Are there any good tutorials or guides that are worth looking into? I can do basic things like sum and whatnot, not much beyond that, looking to gain a little more knowledge.

    Not really needing a specific area either, just want to be well rounded and quicker than I am currently.
    Try Udemy. They have tons of courses that'll help you out in just about anything tech related. Don't pay full price cause it's probably not worth it, but they usually offer steep discounts on a regular basis. Plenty of excel courses for you there.
    __________________
    Andrew
    DavefaninNZ is offline   Reply With Quote
    Old 02-09-2017, 05:58 AM   #110
    BTBaboon
     
    BTBaboon's Avatar
     
    Join Date: Jul 2015
    Posts: 51,323

    Shows Seen: 31

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by justink View Post
    anybody have any good budgeting sheets? utilities, groceries, entertainment, eating out... etc?
    Depending on what version of excel you have, there are some nice templates that are already set up. If you go to set up a "new" sheet, there should be an area with sample templates - usually a "personal budgeting" template will be there

    Let me know if it's not and I can send you something
    BTBaboon is offline   Reply With Quote
    Old 02-09-2017, 06:03 AM   #111
    BTBaboon
     
    BTBaboon's Avatar
     
    Join Date: Jul 2015
    Posts: 51,323

    Shows Seen: 31

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by DaveHead36 View Post
    Been looking to get more familiar with excel. Are there any good tutorials or guides that are worth looking into? I can do basic things like sum and whatnot, not much beyond that, looking to gain a little more knowledge.

    Not really needing a specific area either, just want to be well rounded and quicker than I am currently.
    Microsoft's Excel "help" page has lots of good walkthroughs and tutorials for high level overviews

    https://support.office.com/en-us/excel
    BTBaboon is offline   Reply With Quote
    Old 02-09-2017, 07:46 AM   #112
    DaveHead36
    YNWA
     
    DaveHead36's Avatar
     
    Join Date: Jul 2003
    Location: Texas
    Posts: 6,692

    Shows Seen: 4

    DMB Hub Stubs: 4

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by DavefaninNZ View Post
    Try Udemy. They have tons of courses that'll help you out in just about anything tech related. Don't pay full price cause it's probably not worth it, but they usually offer steep discounts on a regular basis. Plenty of excel courses for you there.
    Quote:
    Originally Posted by BTBaboon View Post
    Microsoft's Excel "help" page has lots of good walkthroughs and tutorials for high level overviews

    https://support.office.com/en-us/excel
    Thanks to you both!
    __________________
    Time flies like an arrow, fruit flies like a banana
    DaveHead36 is offline   Reply With Quote
    Old 02-09-2017, 10:00 AM   #113
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by DaveHead36 View Post
    Been looking to get more familiar with excel. Are there any good tutorials or guides that are worth looking into? I can do basic things like sum and whatnot, not much beyond that, looking to gain a little more knowledge.

    Not really needing a specific area either, just want to be well rounded and quicker than I am currently.
    Here's a list of a bunch of good resources. Most are free.

    https://blog.hubspot.com/marketing/l...10qx2hxi0whl5t
    rconverse is offline   Reply With Quote
    Old 02-09-2017, 03:32 PM   #114
    DaveHead36
    YNWA
     
    DaveHead36's Avatar
     
    Join Date: Jul 2003
    Location: Texas
    Posts: 6,692

    Shows Seen: 4

    DMB Hub Stubs: 4

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Here's a list of a bunch of good resources. Most are free.

    https://blog.hubspot.com/marketing/l...10qx2hxi0whl5t
    Oh man, this is exactly what I was looking for! Thanks!
    __________________
    Time flies like an arrow, fruit flies like a banana
    DaveHead36 is offline   Reply With Quote
    Old 02-09-2017, 03:42 PM   #115
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Hey Rog (or anybody else). I know this one's easy with conditional formatting, is there an easier way to do it? What would the formula look like for the conditional formatting?

    So, I've got a huge workbook for my finances. Tracks all my money in & all my money out. I've written it all from scratch, and it's been a huge work in progress. Just a little side project I'm working on.

    One of the sheets is all 12 months at a glance, and hidden below that is (along with some other random info) all of my projected expenditures and income for that month.

    What I want is if that line has already been paid (I have another sheet with all of my transactions) or the paycheck has been deposited, format the font to include a strikethrough so I can see visually what's been paid without having to look it up.

    If you'd rather I send you the .xlsx I can do that too, there's a lot going on behind the scenes (the values are all pulled from other sheets, for instance, as well as the due dates...) and once you see that it may make it easier?

    If you want screenshots pm me.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-09-2017, 09:03 PM   #116
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    Hey Rog (or anybody else). I know this one's easy with conditional formatting, is there an easier way to do it? What would the formula look like for the conditional formatting?

    So, I've got a huge workbook for my finances. Tracks all my money in & all my money out. I've written it all from scratch, and it's been a huge work in progress. Just a little side project I'm working on.

    One of the sheets is all 12 months at a glance, and hidden below that is (along with some other random info) all of my projected expenditures and income for that month.

    What I want is if that line has already been paid (I have another sheet with all of my transactions) or the paycheck has been deposited, format the font to include a strikethrough so I can see visually what's been paid without having to look it up.

    If you'd rather I send you the .xlsx I can do that too, there's a lot going on behind the scenes (the values are all pulled from other sheets, for instance, as well as the due dates...) and once you see that it may make it easier?

    If you want screenshots pm me.
    The formula would look the same in conditional formatting as if you had the formula in the cell of your spreadsheet.

    If you have multiple worksheets within your workbook, then it might be easiest to just send me the file. I'm happy to help any other way but, if you just want this done, sending me the workbook is the easiest for me and would be the fastest for you.

    rconversejr@yahoo.com
    rconverse is offline   Reply With Quote
    Old 02-09-2017, 09:34 PM   #117
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    The formula would look the same in conditional formatting as if you had the formula in the cell of your spreadsheet.



    If you have multiple worksheets within your workbook, then it might be easiest to just send me the file. I'm happy to help any other way but, if you just want this done, sending me the workbook is the easiest for me and would be the fastest for you.



    rconversejr@yahoo.com

    Pm sent...
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-09-2017, 10:03 PM   #118
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    Pm sent...
    Replied.

    I have to say, I rarely ever see anyone using the CHOOSE function.
    rconverse is offline   Reply With Quote
    Old 02-10-2017, 10:01 AM   #119
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Replied.

    I have to say, I rarely ever see anyone using the CHOOSE function.
    Yeah I found that one a few weeks back. it's so helpful in some situations.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 02-10-2017, 12:25 PM   #120
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    I just sent you the file back with my notes. I think your workbook could use a couple of small changes and then you'll be able to do what you want easily. If you want to PM me your password so I can edit it, I can make the changes and then send it back the way I think it would work best.
    rconverse is offline   Reply With Quote
    Reply

    Tags
    excel

    Thread Tools
    Display Modes

    Posting Rules
    You may not post new threads
    You may not post replies
    You may not post attachments
    You may not edit your posts

    BB code is On
    Smilies are On
    [IMG] code is Off
    HTML code is Off

    Forum Jump


    Want to hide all ads on Ants? Click here

    All times are GMT -7. The time now is 01:34 AM.


    Powered by vBulletin® Version 3.8.14
    Copyright ©2000 - 2024, vBulletin Solutions Inc.


       
    Site LinksAbout AntsAnts MobileTweet Tweet
    Home
    Ants+
    Tour Central
    Search bar
    RSS Feeds
    About Us
    Contact Us
    The Ants Blog
    Advertise on Ants
    Privacy Policy
    Ants on your cell phone
    iAnts
    mobile news
    mobile setlists
    antslive!
    Ants' Twitter
    DMBLive Twitter
    Ants Facebook
    Ants Instagram