1. S

    Calculate start date

    Hi I am trying to calculate the start date of the first operation based on the start date of the second operation. Typically this is always going to be 7 days (5 work days) before. I can do this easily by just deducting 7 from the second operations start date however I have a calendar lookup...
  2. D

    Updating a formula in excel

    I have a formula that correctly returns if a date is a mon-fri, weekend or public holiday. =IF(A11="","",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),"Public Holiday",IF(WEEKDAY(A11,2)>5,"Weekend","Mon-Fri"))) I need it to now determine if the day is a mon-fri, saturday, sunday or public holiday. Can...
  3. S

    Count instances of text where cell matches month/year

    I am trying to prepare holiday data for an import into a resource planning system at work. Human Resources have provided me a spreadsheet of all our sites (100+) and which holidays each site observes from 2019 through to the end of 2025. Sites are listed in column A. The date of the holiday...
  4. J

    Gnatt Chart work Planne

    Hi There I have created the attached planner/job board and require help in doing the following. 1. Under the days of the week. if a date range is entered i want it to auto colour the cells under days of the week. 2. in the engineer list section if i set and engineer to on holiday or sick, i...
  5. E

    possible to have WORKDAY FUNCTION holiday range to include time stamp?

    Hi. = Workday(A1,1,[holidays]) For the "holidays" I have no problem with my list: (1/1/19, 1/21/19, 2/18/19, etc.). However, I am making a stock market calendar with different time increments. Sometimes the market closes an hour earlier to observe the upcoming holiday. Thus instead of...
  6. Trevor3007

    date +5

    hello, is there a fomula to NOT incude bank hols, sat & sundays? for example:- (a2 ) 23/819 (b2) =a2 + 5days ( not inc sat/sun & bank holiday) = 2/9/19 many thanks in advance KR Trevor3007
  7. K

    Holiday Calendar

    I am trying to create a holiday calendar for my office that when the holiday is booked in the table (list) it highlights the days taken in the below calanders. I was pretty sure I could do this by index match until that didnt work that is, so now I am stuck. Has anyone got an idea of how I can...
  8. J

    Index match

    Good morning could someone please help me out with an INDEX MATCH I'm trying to find dates when people are on holiday. my index are DATES on K27:N27. THE NANE J28:J33. THE HOLIDAYS H29:K28 H28 is the name H29 is the letter H so I'm trying to find the name and then the H and return the...
  9. M

    Drop List Sheet Match or Choose Formula??

    Hi All, I want to make a drop down list to pull the data from the month sheets, showing colour box in the Calendar year sheet. But for each Staff is showing only there own data at the time from the drop list. I not sure if I use a "choose formula or match formula", but how do I do it...
  10. M

    Countif function to count as a half

    I have a Key Section see belowH = 1 Full day holidayHD = 0.5 half day holidayI am trying to pull this into a summary sheet to add up. Each staff names are in rows with each have a cell under Jan to a total Holidays, but it adds H as 1, and HD as 1. When I want it to record HD as 0.5 half day...
  11. R

    Exclude certain dates fron holidays in the NETWORKDAYS function

    I've set up a worksheet to calculate holidays. There is a table consisting two columns. On column with dates, which include and days-off (holiday, public holidays and business trips) and another column with types of holiday ("holiday", "Public Holiday" and "business trip"). I use this table for...
  12. K

    Cell to only allow certain text strings.

    Hello, I need help with a code that will only allow certain text strings in the cell. So like data validation however not so restricted. For example restrict the cell to have these: Holiday Course Leave But! I would like users to be allowed to enter other information before or after. For...
  13. K

    Reference Table w/i Add-In ???

    I have an add-in I have developed with some VBA code and 2 tables on different sheets. TableA is self contained (i.e. only relevant to the VBA code w/i the UDF in the Add-In). TableB is a Holiday table that I would like to distribute via the Add-In so other using the Add-In need not maintain...
  14. K

    Data Validation - Text in cell which contains words.

    Hello, I would like some sort of data validation, or vba code to stop people entering the wrong information. I have a list of words which gets coloured in conditional formatting. Sometimes people put in the wrong words. For instance i have the word "Holiday" in conditional formatting turn red...
  15. R

    populating multiple cells from a dropdown list

    I just might not be looking in the right area. I've been searching for a day an a half now and I seem to be stuck searching (Einstein's Crazy Statement inserted her) Let's say I have a dropdown box in cell A5 with a column header of month. B5 is season C5 is Holiday and D5 is temperature...
  16. D

    Formula problem and a pivot does not help...please can you?

    Hi I have an excel query and i can not figure it out: I have a list of names in column A and Dates along in Row 1. And under the dates by the names are zeros or BH (Bank Holiday), W (worked), H (Hol) & HF (Half Day Hol) I am trying to get results by looking at the name i want to know when...
  17. S

    Multiple IF's formula Help

    Hi All I'm wondering if someone can help please? I will have to explain sheet as I'm on work PC so unable to upload example spreadsheet. I need a formula that will calculate/count how many holidays someone needs based on their shift pattern. Warehouse is open 7 days a week and workers are...
  18. F

    Cross checking booked holiday with project dates

    Hi I have sheet1 (project dates) set up like this: Start Date | End Date | Initial 15/01/2019 20/01/2019 BF 16/01/2019 16/01/2019 FB 01/02/2019 14/02/2019 BF 15/02/2019 28/02/2019 FB ...and sheet2 (booked holiday) set up like this: Holiday Date | Initial...
  19. P

    Conditional Formatting - To color following Mon as Public Holiday if Public Holiday falls on Sun

    Hi, I have a workbook with 3 worksheets - 1st worksheet shows a yearly calendar, 2nd worksheet shows a list of course schedule, i.e. course name, course duration, start and end date, 3rd worksheet shows Public Holiday of that year. Help needed to set a rule using conditional formatting to...
  20. B

    VBA error while sorting

    Hi, I have a (what I thought was) very simple line of code that is causing my macro to error if the macro is run from a different sheet to the sheet that the data is being worked on. I originally defined it within a block of With Sheets... code, but when it errored I defined the line itself but...

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