holiday

  1. 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...
  2. 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...
  3. N

    VB to add text to cell based on text of another cell ....

    Hello All, I have a workbook that is used to track employee absence. There are 13 worksheets, 1 for yearly totals and 1 for each month of the year. Using CF, I am filling cells that equal holidays but would like to add an "H" to each cell in each column that represents a holiday. Right now...
  4. S

    Copy and paste from one sheet to another based on a cells value

    Hi All, I have a spreadsheet that populates the employees time and attendance. I am trying to create a new tab that will only pull the data for lateness's from the main tab. <tbody> Area Badge Payroll No. Name Basic x.1.25 x1.5 x2 O/T Total Late Holiday Sickness Bank Holiday 04 Unpaid Holiday...
  5. T

    Auto populate holiday tracker from year planner

    Hi, I have a sheet "Planner" where I can input the holidays taken by employees. All these sheets currently work fine as they are if I were to input data manually. What I am looking to do is auto populate sheet "Holiday Overview" cells C4:N28 with the hours holiday taken which is based on the...
  6. C

    IF formula with 3 criterias

    Hi, wish to ask if anyone could help me draft a formula for the following case: <tbody> Date Day Malaysia Hong Kong Dealing Valuation 1/1/19 Thursday Dealing if Friday is either Malaysia or HK Holiday 1/2/19 Friday Dealing if Monday is either Malaysia or HK holiday 1/3/19 Monday...
  7. H

    Return date of 1st Mon in month which is not a Bank Holiday/weekend

    Hi all In cell J4 I have the date 01/11/2018. In cell B15 I want to return the date of the first Monday in the month of November (calculating off the date in J4 (which will always be the first of the month)) BUT where the Monday is a Bank Holiday return the date for the Tuesday, if the bank...
  8. J

    condition format

    Hello to all, I have a calendar and I am looking for help to show stat holidays I have a list of days and what I am looking to do is highlight the cell to show the holiday on the calendarcan so one show me the way it would be a big help thanks
  9. K

    Background colour of cells to change based on value.

    Hello, I have a document (shared) which we use as a calendar and to keep track of people and where they are. It has all 365 day on it and with over 100 people. I would like a vba to search the document and change the cell colour based on the value. For example: Red if someone is away on...
  10. B

    Formula Master HELP!!!

    I have an elaborate payroll budget sheet. I need it to now calculate vacation & holiday liability Starting with the holiday liability I need to to show was our 2018 liability by employee based on their start date, but I also need to not calculate days if they were terminated. Someone who is over...
  11. D

    Determining work days and date ranges (Gantt related)

    I'm working on a gantt chart where I want to be able to determine the number of "work days" which occur as of a certain date (which could be a weekend or holiday start). I have to subtract non working days such as holidays, which are listed in another tab. The function I am using is WORKDAY()...
  12. R

    Planning Board that deducts holidays when added

    Hi All, I hoping for some advice on how to make a team planning board which reduces the employees holiday entitlement when a holiday is added to the board. Please see below picture to get an idea of what I am looking to create :) - the sheet only needs to capture holidays as a reducing value...
  13. D

    Macro to replace formula

    Hi All I am hoping that someone out there will be able to help me. I am looking tochange a nested formula to a macro due to the amount of conditions that need to be met. I have an export out of a system that is rather "ugly". This schedulehas stuff in like holiday, Bank Holidays, Sickness and...
  14. S

    VBA to search string and stop when found once

    hi, in Week 2 column, i have column Holiday (I column). In week 1 tab under same workbook, i want to add another column next to column I, if there is any holiday in Week 2. Else end if. not able to get it with below. note: Macro to be moved to next step, once column is inserted. Else it will...
  15. S

    Staff Planning

    Hi I have a staff planning tool which I have created in excel Database. Is fed from a data input sheet, and houses formulas to work out lots of variables. Sheet 2 is a list Column A - Employee Name Column B - Inclusive Holiday Date From Column C - Inclusive Holiday Date To The...
  16. B

    Change date if it is a weekend or bank holiday

    Hi, I have set up a pay date sheet which shows when monthly pay is due up to 2030. I want to bring the date forward to the prior working day if the pay date falls on a weekend or bank holiday. Bank holidays have been set in a range called "Hols".. I can get it to move the pay date to the...
  17. J

    Which formulae to use

    Hi Guys, I have an export which gives me holiday start and end dates e.g. 01/08/2018 - 20/08/2018. I need a formula which will enable me to look at all the dates in the range and enter the value 1 in each corresponding date column on my holiday chart. Can anyone recommend a good formulae for...
  18. O

    Counting values based on a criteria in the next cell

    Hi, I update a roster for the entire month in the attached format and intend to capture shift details. Row 1 indicates the holidays marked as 'H' and row 4 captures the individuals working in the night shift. For costing purposes I want to count how many times BB has worked the night...
  19. C

    Autofill based on 2 other cells

    Trying to build a sheet to assist in calculating available working hours vs required hours for a job. Please help with a formula that will calculate Columns D and E based on the logic below: Sheet is setup like this: <tbody> Date Day Status STD HRS OT HRS TOTAL HRS 2-Jul WEEKDAY...
  20. L

    Power Query: Lookup to See If a Holiday Falls Within a Work Week

    I have a table with a Date column that has the beginning of each (U.S.A) work week, which starts on a Monday, like 7/2/2018, 7/9/2018, 7/16/2018, etc. In another table I have a list of U.S.A Holidays, for example 7/4/2018 (Independence Day), 9/3/2018 (Labor Day). I would like a custom column...

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top