year

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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)...

<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...