1. D

    Creating relationships in Power Pivot

    HI, recently could not get the relationship form date to fact table in the right direction , i had several tables with date columns and had created a calendar, but the direction was always wrong, so I then tried joining to one of the other tables this worked fine, so I deleted from data...
  2. S

    Excel DAX question on Quarter to date sales.

    Hi, I've created a dCalendar in Excel with all the dates for several years. I can pull the slicer and use it in my pivot tables. I can select the date, month, year... But for the QTR our year starts Feb 1. So I need QTR to date sales. That would be 4th QTR today all sales from Nov 1 to today...
  3. L

    Excel Calendar using data from a list.

    Hi All, I have a list of events that I want to be pulled into a calendar so it can be displayed easily. The calendar is just a generic Excel template. Is there a formula that will be able to pull the info from the table and place it into the correct dates on the calendar? The calendar...
  4. S

    Trigger email and retrieve data from other cells when a cell color changes.

    Not sure why the code is not working. Could anyone help? I am trying to trigger an email when a call changes colors and at the same time retrieve information from other cells. For example, if cell J38 changes to red email content will be info from cells, A38, J37, and B35, (Tricia, 9, Dec 23)...
  5. U

    Week number in the calendar

    Dear users, I would like to create a calendar in Excel. Here are the basic rules: 1. The financial year runs from April to March 2. The week runs from Monday to Sunday 3. The week numbers are based on the Sundays in that year. for example, week commencing 30 March 2020, Monday ends on a...
  6. S

    Colorize a few cells by choosing a specific date (VBA or Conditional formatting)

    Hi How I can do this ? For example, if I click / choose a date 11/04 in a range D2:J7, it should colorize the same date (and descriptions in 2 near cells) and same color in a range L2:N15 if the same date exist in column L. Is it possible by VBA or Conditional formatting? Thanks
  7. J


    MARKETCAL2 will generate the days the market is closed for the year requested or the current year if that option is omitted, and is designed to work in conjunction with the MKTDAYS Lambda function to build an array of dates the market is open. It will generate a two column array with the name of...
  8. J


    MKTDAYS is the second of two posts intended to alleviate the problem with the STOCKHISTORY function where it returns an error when attempting to provide information for an invalid date as reported by @DRSteele . The first post was MARKETCAL which can be used to provide the required array of...
  9. N

    Sequence formula on every second row for Calendar type view

    Hi, is it possible to adjust the =SEQUENCE() formula so that only every second row is filled? I am trying to create a calendar within excel so that the dates automatically populate in the correct cell relevant to the day of the week. Picture attached. However the sequence formula spills...
  10. H

    Date custom formatting for 30 days in each month

    Hey guys, My government considers 30 days for each months when it comes to payments (ignores the 31th days of some months) which proved problematic for me to make some calculations regarding the salary of the workers, I need to know if there's a way to create a custom calendar system or modify...
  11. welshraz

    List of Dates if COUNTA = 5 and greater than Today()

    I have created a calendar to easliy track leave etc. of a team. There is conditional formatting in place that highlights dates that may cause us issues. What I would like is a formula that finds the dates that meet this criteria, and lists them. I do not know if what I am asking is...
  12. S

    Annual Leaver Tracker

    Hello, I am trying to make an annual leave tracker for my partner, which displays the following things. Total Annual Leave Annual Leave Taken(Historic) Annual Leave Taken(Future Dates) Annual Leave Used. The issue I am finding, is that because her annual leave is calculated in hours, like...
  13. M

    Using date picker to fill a different cell with a color

    Hello all, I hope this isn't a repeat question (I searched beforehand) but I would like to create a planning chart where I have a start date column and end date column and have those dates and everything in-between filled in with a color on a timeline. This would speed things up and eliminate...
  14. D

    DATEDIFF adding day of year number power bi

    Hi, I'm trying to add the day of the year number in a power bi calendar , and keep seeing the following; DATEDIFF (STARTOFYEAR ('Date' [Date]),'Date' [Date],DAY)+1 I have tried using this and just get errors, "Day of Month", DAY([Date]) , "Day of Week", WEEKDAY([Date] ,2) ...
  15. D

    Power Bi matrix Date filtered down when measure dropped in

    Hi, This is going to be a bit vague, I had a matrix containing Month and date from 'calendar table' and when I dropped the total sales measure into value it was flited down to one row showing the grand total. I tried to replicate the situation but haven't been able to do so. I'd copied the...
  16. G

    BusinessDay List in Calendar Table

    Greetings Excel Community, I am creating a Calendar Table from Scratch and I am wondering if there is a way to create a running list of "Business Days" in the year. For example: 01/01/2022- 0 (Not a Business day as it is a Weekend) 01/02/2022- 0 (Not a Business day as it is a Weekend)...
  17. D

    DAX calendar in pbi

    Is there a way of putting the date column into a matrix that is not a hierarchy, I can do this using the date column from the fact table, but would prefer to use calendar table as I've gone to the trouble of making it, essentially the left , which uses the calendar table to look a bit more like...
  18. D

    DAX running total using 'Calendar' table that respects filter context,

    I hope I've got my row / filer the the right way round, I created two measures one using the dates from the fact table the other from the calendar table RTM using C = VAR mcdate = MAX('Calendar'[Date]) VAR anser = CALCULATE([Tsales],FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<= mcdate))...
  19. L

    Auto recognize weekends with month as row/day as column?

    Hello, I have a worksheet set up like this for us to paste in data we get from a daily report, and at the end we will have some percentage calculation, however there will be some columns with no data since they are weekends. Is there a way for excel to recognize those in this format and put a...
  20. R

    Calendar help

    With the A1 cell name equalling the name of a month how can I get the A2 cell to give me the first day of that month? or if easier I just need a calendar that will allow me to change the cell dates by changing the month in A1.

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