1. W

    If date is in certain month return year range (ex. 2018-2019)

    I need a hero. I have a spreadsheet spread over a few years and am looking for a formula to help with the following. We have what is called a "bid season" that is from October through May. I am trying to have a value returned in column F to combine years if a date in column C is between Oct-May...
  2. M

    VBA Match Month & Year in Row to Month & Year from another cell

    Thanks in advance for any help! I'm trying to locate the month and year contained in a row of one worksheet that matches the month and year contained in a cell on a different worksheet. Then copy the matching column to a third worksheet. With the below, I'm receiving a "Type Mismatch" error on...
  3. E

    Get year from personal number (10 digits)

    Hi, how can I get the birth date (YYYY-MM-DD) from a personal number that is just 10 digits? The personal number is like this: YYMMDDXXXX (9001121515). It has no spaces or anything, just 10 digits. I have this formula and it works if the person is born before 2000. But for 2000 and afterwards...
  4. Johnny C

    Excel 365 ProPlus - saved links giving 'old' values?

    I have a model that generates charts for PowerPoint. The values in the table for the chart are linked to another workbook. Each day the models are copied to a new folder with the date in, as we need to keep a close audit trail. So the value in one of the cells could be ='F:\3 Year...
  5. T

    Conditional format if year in cell's date is same as current year

    I have a cell that contains a date in the format 21-Feb-21. I would like it to shade it green if the year in the date is the current year. I am going to have a second rule where if the year in the cell is before (less than) the current date than shade red. I believe I can do it simply but...
  6. H

    Select Filter based in Value

    I would like macro to select the filter in A2 (Financial Year") on sheet summary if Cell c10 on sheet "Data" is zero, otherwise select highest year from Filter Your assistance is most appreciated <b></b><table cellpadding="2.5px" rules="all" style=";background-color...
  7. H

    macro to select year in Filter omn Pivot table

    I have a pivot Table on sheet "summary" Where the value in C10 in sheet "Data" is zero, the year to be selected on Pivot filter in B2 must be blank , otherwise select highest year It would be appreciated if some could provide me with code to do this <b></b><table cellpadding="2.5px"...
  8. F

    Exclude year with sumproduct and subtotal

    Hey guys, I've recently started to use the sumproduct and subtotal functions to make my data responde to filters and after some struggle i made it work, but right now i can't use an expression that excludes by year. So i need an expression that gives me the number of dates in a column that...
  9. E

    Yearfrac problem

    Hi all, I'm using the YEARFRAC function to calculate the fraction of the year worked. Based on 13/5/2019 - 31/07/2019 the function returns <colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <tbody> 0.2166666667. </tbody> However based on...
  10. L

    Same Store Sales Analysis using Measures

    Hi everyone, I'm new to this forum but have spent a lot of time reading through other's posts, which are often very helpful. I haven't found a definitive answer to the following question and hoping you all might be able to provide some guidance. I am pretty new to Power Pivot - thank you in...
  11. H

    Amend Formula to extract year from Cell

    I have the following formula below =INDEX('C:\My Documents\[Comms2019.xlsm]Comms by month'!$A$11:$N$11,MATCH('C:\My Documents\[Comms2019.xlsm]Comms by month'!B1,'C:\My Documents\[Comms2019.xlsm]Comms by month'!$A$1:$M$1)) I would like to amend the formula to extract the year from cell P1 so...
  12. leopardhawk

    Get External Data (long shot question!)

    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to by 'Get External Data' every year on January 1 at 00:01 local time? The following URL is currently in use on a worksheet within my workbook...
  13. F

    Today's date - 5 years

    Hi all - I NEED SOME HELP!! I have 3 drop down lists - DATE (D2); MONTH (E2); YEAR (G2) I have annual leave allowance based on time served - (Q2) 25.0 (<5 years service); (Q3) 30.0 (>5 years service but started after 30/4/2013); (Q4) 31.5 (>5 years service and started on or prior to 30/4/2013)...
  14. P

    Average days by type and year

    <tbody> Type Days Year 34 12 2017 34 24 2017 65 11 2017 66 44 2018 34 23 2018 66 13 2018 34 44 2019 65 8 2019 66 31 2019 </tbody> I want the average days by type, by year. So for example, in 2017, the average days for type 34 is 18. The average days for type 65 is 11. I...
  15. K

    Check if date is Sunday

    Hi Everyone. I have a UserForm where the user must update the Public Holidays (South African) for the year. Basically the user must only change the year and then the date of Easter Friday. All the other dates will auto update in the relevant Table ("tblPPH") In this UserForm, there will be a...
  16. D

    Future employee salary/rasies

    I know how do do a simple salary schedule to to calculate year over year salary. I've tried googling, but came up short. Does anyone know of a formula that I can use to calculate year over year salary expense average? I'm trying to create a template to use for new-account bids to factor in...
  17. L


    Hello Guys. A quick and simple question (too difficult for me though). I have a column with dates (Column A). I need to count the number of records that are from the certain year (YEAR formula) and certain week (WEENNUM) without using any additional calculation columns (file is big, a lot of...
  18. V

    Struggling to produce report based on horizontal table.

    Hello All, This is my fist post here so please accept my apologies if this doesn't followthe usual format for asking questions. I have a worksheet which I use to monitor staff holidays and plan resourcesaccordingly. Part of this sheet is a table with a list of about 50 staff on theleft and the...
  19. I

    Could you check my code, userform to worksheet please

    Hi, The code is shown below. I have a userform with Comboboxes ComboBox1 is MONTH ComboBox2 is YEAR Month is to be inserted into cell A3 Year is to be inserted into cell C3 When i press my transfer button the YEAR isnt shown on the worksheet but the MONTH is entered into cell A3 Please can...
  20. D

    min formula

    i have a formula "=MIN(D5:SJ5)" but D5 starts at 11/02/17. Now I gotta change it to 11/01/18 since it's a new fiscal year for there a way to make this dynamic? that is, make it such that I dont have to manually look for where 11/01/18 (which is currently cell IS5) and change the...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back