Posts by billyj

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Convert Numbers Representing Dates To True Dates

    Re: Convert Numbers Representing Dates To True Dates


    Daddylonglegs,


    WOW this works perfect. You are correct I messed up on cut and pasting. I can move forward with my project. The only question I have is in the future should I figure out how to do this using VBA or is using formula's faster? Thanks appreciate the assist.



    BillyJ

  • Convert Numbers Representing Dates To True Dates

    Re: Convert Numbers Representing Dates To True Dates


    Daddylonglegs,


    thanks for the assist but I could not get the formula to work. Not sure why. In column "M" it should always be positive. L will always be greater than C. Thanks I'll conntilue to play to see what I'm doing wrong.


    Thanks BillyJ

  • Convert Numbers Representing Dates To True Dates

    Thanks in advance... I import data from my works server and dates are represented in text. example text format is yyyymmdd/19790310. What I'm trying to do is add and subtract from this date. I was trying to utilize formula and havent got close. I have a couple of parameters:


    In column L I would like to represent the following


    if column "B" is "A" add 38 years to column "J" dates are represented as TEXT
    if column "B" is "B" add 35 years to column "J" "
    if column "B" is "C" add 30 years to column "J" "
    if column "B" is "d" add 30 years to column "J" "


    Column "M" I would like to find the difference in column "L" and "C" I know days will be difficult it can be represented in years and months only.


    I have no preference on whether I use VBA or a formula but have failed on on formula and was attempting to use VBA Case method but no success. I have 10000 rows of information and am open to any ideas attached an example of my data.


    Thanks
    Billyj

  • Conditional Format Based On Background Color

    I'm attempting to write a conditional format formula that will change the background color based on multiple color options on in a row. I have cell A:c as first, last and middle name. I utilize the rest 40+ cells that provides dates that are conditionally formatted to based on dates. I've approached this trying to reference the columns dates and cannot figure it out. any ideas would be a bonus.


    Heres what I would like to do:
    If any cell in from D:Z has a background color of red, yellow, green, and none it will change the background color in cells A:C. Its easy to reference one cell but not multiple. I've attached a sample of what I would like to do and I manually changed to cells A:C to show how I would like to show my results.


    Thanks BillyJ

  • Show Values Of Charts Data Points

    I've been working on this chart for a while and can't seem to get my x and y axis as well as my series corrected. I have time and distance but since my distances are small I cant seem to make sense on how best to depict my data. Right now it is shown as data points but would love to show value. I'm essentially lost so any advice would be welcomed.


    Thanks


    BillyJ

  • Color Cells Based On Days Date Difference

    I'm just drawing a blank and would appreciate some help with a formula for conditional format.


    In cell "C5" I have a date for example 1/21/2009
    in cell "D5" I have a date for example 12/21/2008


    WHat I'm trying to do is use conditional format to color the cell in D5 with the following criteria:


    60-90 day or greater difference highlight green
    30-60 day difference highlight yellow
    0-30 days difference highlight red


    I'm just drawing a blank.


    Appreciate the help.


    BillyJ

  • Enter Formula With VBA

    I previously requested help and several folks helped me out. I've found that if I can convert to a VBA It would be easier. I can record the Macro but was wondering if anyone had any better ideas. Heres the formula I'm trying to work into a macro using VBA. Thanks


    = DATE(LEFT(J3,4)+CHOOSE(MATCH(B3,{"CAPT","CDR","LCDR"}),38,35,30),MID(J3,5,2),RIGHT(J3,2))


    THanks very much
    Billyj

  • Convert Imported Dates & Add Periods Based On Conditions

    Re: Convert Imported Dates & Add Periods Based On Conditions


    Thanks to everybody. I've tried every example and can't seem to get it to work. I'll keep chugging away on this project.


    Thanks Billyj[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I worked and found my typo's. I got these two to work.


    =DATE(LEFT(J2,4)+ LOOKUP(B2,{"J","K","L"},{38,35,30}),MID(J2,5,2),RIGHT(J2,2))


    = DATE(LEFT(J3,4)+CHOOSE(MATCH(B2,{"J","K","L"}),38,35,30),MID(J3,5,2),RIGHT(J3,2))


    The only problem is that if B2 doesn't match it errors out at 30. Is this the way its suppose to happen. I figured it would N/A or Value because it didn't match.
    Thanks and appreciate all the help.


    BillyJ

  • Convert Imported Dates & Add Periods Based On Conditions

    I'm working a problem that I know shouldn't be so frustrating. I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following.


    My data looks like this in text:
    yyyymmdd


    I desire to look in column "E" and I have 3 different variables. "J", "K", "L".
    If column "E" has a j add 30 to the years; K add 35 years and L add 38 years.
    for Example:


    19630923 j = 19930923 k = 19980923 L = 20010923


    I can use either formula or VBA but originally thought that formula was the best approach but have struggled mightily. Thanks attached a small example.
    BillyJ

  • Color Dates With Current Date as Criteria

    I'm really struggling with conditional formatting when I use dates. In column "A" I manually enter date; In columns "B" it projects a date 45 days earlier. In column C it projects 30 days earlier. I would like to use conditional formatting to highlight the background in columns B and C Yellow if its within 10 days of timeline or Highlight in red if its late. I've attached a quick workbook with examples.
    Thanks as always.


    BillyJ

  • Set Automatic Calculations With Selection Of Sheet

    Re: Set Automatic Calculations With Selection Of Sheet


    First I apologize for any confusion on my question. What I'm trying to do is turn on auto calculation when I select a sheet3. I can do that... what I would like to do before I turn on auto calculation is run the following macro on sheet1. I gather data from sheet1 then would like to calculate. Thanks Billyj

    Code
    Sub Sortbypeprorprd()
' Keyboard Shortcut: Ctrl+p
   Cells.Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("I2") _
        , Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

  • Set Automatic Calculations With Selection Of Sheet

    I have a sheet that I need to turn on auto calculation when selected. The problem I need to turn on auto calculation as well as sort another sheet "test" I do not know how to as the other portion of the code. attached will not work because it just runs in circles thanks in advance.


    BillyJ


    Code
    Private Sub Worksheet_Activate()
'THIS IS A SEPARATE SHEET THAN WHAT I NEED TO SORT FROM
    Application.Calculation = xlCalculationAutomatic


    Sheets("6180").Select
    Application.Run "'BILLET-SLATE P414D working copy.xls'!Sortbypeprorprd" 


End Sub