1. R

    Dynamic holiday range formula

    Hi, I have the following formula which is inserted on sheets through VBA code: =SUMPRODUCT((B3:AF3="AD")*(WEEKDAY(B2:AF2,2)<6)*(ISNA(MATCH(B2:AF2,HolidaySheet!A2:A3,0)))) The A2:A3 range contains two holiday dates which are on a separate worksheet. The holidays change depending on the year...
  2. R

    Macro to add month sheets containing days

    Hi, I have a VBA code (see below) for a macro button to add month sheets in a workbook. Now the month sheets are sorted from January to December, but I would like them to be sorted from December to January; while still keeping differently named sheets at the end of the worksheet tabs. After...
  3. D

    Convert date automatically to “mmm” format

    Hi all, Can I know how to get month in “mmm” format if I have a date in its previous column. For eg: column A contains date in short format. I want column B to show only the month in “mmm” format based on the date in column A. Similarly column C will show only the year in “yyyy” format. How to...
  4. MrDB4Excel

    IF(ISBLANK Returns #VALUE!

    In this workbook, I have tried in vain to get cells to show nothing when there is no value in specific cells. Cells A2 to A8 show a date while cells A9 to A11 are blank. Cells C2 to C11 has the formula =IF(ISBLANK(A2),"",A2); obviously each successive row the row number changes, for example: In...
  5. Martin_H

    Slightly modify the code [VBA]

    Hi team, I would like to slightly modify the code below, which is a small part of bigger macro: If Month(Date) < 3 Then MsgBox "Not yet available.", vbExclamation Exit Sub End If So basically, if today's month is February and I am trying to run macro specified only for the month of March...
  6. E

    Pivot table Show products sold only in one year

    Hello, I can't post a screenshot with my pivot right now, but please consider a simple tabular pivot table with 2 columns. Column 1 contains years 2000-2020( group by date feature) and Column 2 has some product names sold in that year from column1. No agregation, only this 2 columns. (I'll...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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 Book1AB12Financial Year(blank)34Row LabelsSum of Capital Cost5(blank)6Grand...
  13. 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 Book1AB12Financial Year202034Row...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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)...
  20. 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...

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