Posts by DaveSharpe79

  • Vlookup and If formula

    Morning,


    Can anyone please help me, I have been scratching my head for ages and looked all over the web and I cannot fathom how to do the formula I need.
    I can do a very long multiple if formula, but their must be a better way, maybe combining a VLookup and an If formula.


    I have a very large sheet of dates when projects need to start and a diary from our customer.
    To report to our customer when this work is done it would be better to have a mechanism to define what year & period that the project falls in (column A).


    The formula I would like looks at the date (column B), then by using a formula it looks to see what date range it falls between (columns f & G), then the output finds the correct year & period (column H).


    best regards
    Dave

  • Excel SortCut Ctrl & S

    Ahhh.
    Its a behind the scenes Macro.
    Great I never knew that you could macro short-cuts and no searches on Google suggested this.


    I have looked into it and found the macro.


    Many thanks.

  • Excel SortCut Ctrl & S

    Hi,


    Can someone please assist, I have a spreadsheet I have inherited and there seems to be something funny about the ctrl & s shortcut.
    It doesn't save the sheet, it sorts it.


    The spreadsheet used to sort perfectly fine when the shortcut was implemented.
    Then I moved a couple of the column around and now it doesn't sort it correctly.


    Is there a setting this shortcut to sort the correct column.


    best regards


    Dave

  • Analysing Data between multiple ranges

    Hi,
    Can someone please help me.
    I am trying to analyse data between 2 different ranges, I have a list of customers and their invoice/ payment dates.


    I have used a SUMIFS formula to group by pay date i.e 31>60;61>90 etc.
    However, I would also like to add a date range as we hold a few years of data, extending the SUMIFS formula doesn't work.
    In the sheet I have attached, I would like to only include the data between the date range "C3083" & "D3083"


    best regards


    Dave

  • Excel - Sumifs/data collection date range query

    Could someone please help.
    I have been racking my brains around a dilemma that I think involves an extra sumif, I have spent days on this but just cannot figure it out despite checking every webpage/forum but no joy.


    The attached excel sheet attached is required to compile time-sheet data by category, person and then by month.
    I can figure out the category and person, but cannot figure out the last i.e. by month.


    I would like to be able to insert a formula that can the manually change a date at the top of the results page and this automatically adjust the results (in this instance the results for November '16).
    A complication i think arrives as the time-sheet data has 7 date columns (Monday>Sunday), as the exported data date only shows the week commencing date..


    best regards
    Dave

  • Excel "=Month" Settings

    Re: Excel "=Month" Settings


    Hi,
    Thank you for replying.
    I do not know why whenever the number of that specific cell is changed and you type in "=month" on any cell in any tab the same number appears.


    Yet, when you clear the cell, by copy/pasting a blank one over it, then you type in "=month" on any cell in any tab the value "0" appears.
    There must be something behind the scene in that cell, I just dont know what it is.


    best regards
    dave

  • Excel "=Month" Settings

    Hi,


    Can someone please help, I have come across a strange situation on an inherited Excel sheet.
    When I type in "=month" on any tab or cell anywhere, the same month appears from the first tab, cell "B1"


    I have no idea how/why it does this, and cannot find anything on google or this forum to help explain it.


    I would like to be able to do this myself in the future on excel sheets and would to know how it was done.


    The excel file in query is attached.


    kindest Regards
    Dave

  • Convert Time into Hours in Excel

    Can someone please help


    I have attached a section of my timesheet report that come from SICON, the report shows the hours worked in each day on different elements for each week of the month.


    When I pivot rather than show 8hrs is represents 1/3 (0.333) of a day, I would like to be able to convert the sheet into actual hours worked and pivot this.
    I need to report of the hours worked by each member of staff for the month, also hours on each project for the month.


    best regards
    Dave