How to find five consecutive working days for the staffs who worked only in night shift basis and if this comes true then to find other night shift for particular staff based on Bank ID and putting those data's in new excel sheet believe this can done through a macro.
Its new assignment to me to find the staffs to make the inconvenience allowance for the staffs who worked in night shift for five consecutive days and if they worked for five consecutive days then they eligible for other night shift days they have worked.
Given the data how it looks in our attendance. In the below data night shift criteria true stands for staffs who worked in night shift and false stands for staffs who worked in day shift.
I am tracking attendance of individuals in an Excel spreadsheet. I would like to determine how many consecutive days each individual has attended. The spreadsheet has two pertinent columns which contain the individual's name and the date of attendance. How can I determine the number of consecutive days of attendance?
I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.
The values in column BN are text with either of those names.
I need to return two values in cells BO7 and BO8 respectively:
1) The number of consecutive days back from the most current day that have been "Over" the limit
2) The date going from the most current day that the first "Over" occurred
I have a range of data but the important pieces are
Column B - list of employee IDs. Column C - Date
I have sorted the data by column B with secondary sort for column C oldest to newest date.
I want to know the number of instances where an employee has 3 consecutive days in column C. Only count 3 consecutive. If the consecutive days go over 3 (such as 4 or 5 days) I do no want to count these.
I am using Excel 2010. I am tracking client orders on a spreadsheet. I would like to offer a 60 percent rebate on revenue from orders placed between the first order date and 30 calendar days thereafter. On day 31 and continuing through day 60, I would like to offer a 40% rebate. On orders placed on day 61 through 90, I would like to offer a rebate of 20%. From all of the orders placed by clients who start ordering on different dates, I need to sum their order revenue in these three time periods. I have the individual client order data arrayed on my spreadsheet - one order per row.
I am capturing the unique client ID number (Column A), the order date (Column B), and the associated revenue (Column C). Keying off of their unique ID number, I would like to calculate the sum of how much revenue is generated in calendar days 1 through 30, days 31 through 60, and days 61 through 90 from the very first order date from any given client. The start date can be any date in the month, so I can not rely on any calculation that uses the calendar month. Orders are not necessarily placed every day. Multiple orders may be placed on any given day, and then no orders may follow for many days afterward.
In A column the date something is received will be entered.
In B column there is a drop down list that has "insured" "Fee" "Aged Debt" "Other".
In C column I am trying to get another date to populate dependant on what is picked from column B, so if Insured is picked then 1 working day needs to be added to the date in column A and populate in column C. If any of the other 3 options are selected 5 working days needs to be added to the date in column A and populated in column C.
I have been using this formula: =IF(B2="","",IF(B2="Insured",A2+1,A2+5)) however this is not working days and I am stuck how to get to add working days
I have a table with 2 date columns, and I need to subtract one date from another to give me the number of days the job has taken, but this also needs to exclude the weekends. There is no hours involved.
I.e. Start Date Finish Date Days Taken 22/Jan/2009 27/Jan/2009 4
Is there a formula that i can use to work out the days taken. there is multiple rows of information, the above is an example.
1. Find the working days between 15th Nov 09 to 28th
2. Add a column that shows the date after a number of months from today date. The number of months from todays date for each of the country is mentioned in column No. of Months after which revised.
3. Find the date our training will end using excel considering the fact that SAT and SUN are holidays. (Use the date when your training started)
I receive an extract from our Financial System monthly which list all the balances for each date of the month (both workings day and week-end(Sat. and Sun.)).
I would like to create a macro which will extract only working date (Mon.- Fri) and the balances in the columns.
I'm currently working on a file that has employee holidays/sickness etc. What I want to do is be able to see is a DATE FROM and DATE TO and next to it the total amount of working days between and including those dates. On top of the since employee's sometimes like to take half days I need it so that it can determine half days as well.
So for example I have an employee going to be off work from 29/10/2007 until 09/11/2007 12pm which is 10.5 days in total, but that includes weekends when I don't want it to.
Also it comes out as 11 days at the moment because I can't figure out how to get it to determine is it is half a day.
What formula should I use or if someone could create an example that would be really usefull :D
Create a formula to count number of days an employee work (in 25 working days and 5 Friday as rest day month):
Example:
If employee works 25 days during weekdays he will get counted as = 30 days If employee works only 22 days during weekdays with 3 absences during weekdays he will get counted as = 27 days. If employee works from 1st to 24th and 25th to 30th absence he will get counted as = 24 days.
Other examples and output desired I attached here: Sample.xlsx
I have a worksheet where in column B I have a vendor name, column E I have a date value and in column AF I need to calculate a due date based on 7 working days for vendor A and 14 days (not working days) for vendor B. I've tried to write an IF statement but can't do it. Can anyone suggest the right combination of IF/AND statements that would do this? I attach the workbook should my useless attempt at explaining my problem be unclear...
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
I am using Excel 2010. The template only has 5 working days in it, but I need to set 6 working days (Saturday is also working day). Wondering, how to get this working without spoiling the functions. Also the date format is in US , need to change to UK without disrupting the Formula/Functions.
I am trying to do a conditional formatting for the number of working days. What I am trying to do is:
Example:
As in the row D7 I give the actual workdays required to finish the task the Gantt Chart gets a specific formatting on the right appears for the number of working days.
Problem:
I want to avoid the formatting if there are any weekend (Saturday or sunday)
Suppose I give number of working days as 20 then there would be 6 weekend days so it shall add to the overall actual working days but again sinnce we are adding 6 additional days one more weekend day would come in between so the formatting shall take care of this.
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.
I'm using Excel 2003, and successfully working out start date (T2 in the formula below) plus duration in days (U2) minus any non-working days listed in a seperate worksheet. =WORKDAY(T2,U2,Holidays!C6:C17)
Each 'task' is on a seperate row, and the lead officer is named in Column E. How can I get the work day function to include the non-working days for each officer as well as the global non-working days in the seperate worksheet? I thought about using a vlookup, but that only matches the first non-working day for each officer rather than all of the non-working days.
I have a spreadsheet stating a date that my company will deliver a product by but due to a set of certain circumstances we need to pull forward the dates by 3 or 5 working days. Basically it needs to show the dates below but minus 3 days unless this includes non-working days (i.e. weekends)
I am working on a "Salary Payment voucher excel Userform".
How can i do it, if i don't want manually fill in the number of working days into the textbox myself, Can i have the userform to automatically fill in the number of days when i initialize the userform.
I have an excel sheet where I need to insert the working days of the current month in a row. The working days are from Sunday through Thursday, that is, 5 days a week.
For example, for the month of July, 01 is Tuesday and the week ends on 3rd. So the row should look like this:
COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F COLUMN G
[Code] .....
What I'm trying to do, is add a formula via VBA in the "FTE" column (Column Q), which is as follows:
Search row 5 in columns C:N and find the month which matches the current date.When a match is found, take the corresponding value from row 4 and multiply this value by the value in column P.So using the above as an example, the formula in column Q on row 5, would be 22 multiplied by 32.05 giving a total of 705.10.
VB: Sub Extract()
Dim ws As Worksheet, LastRow As Long Const StartRow As Long = 5
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects")) LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
I have a access table with two fields named as "Month" and "Bank Holidays". I have put names of the month in this table for 2014 and the corresponding bank holidays of each month. Now I want to write down the piece of code that will execute when a button on the userform is pressed and it should check the current month of the year and then display the total working days ( After subtracting bank holidays of that month and weekends of that month from the total working days of that month).
I need a function to work out what the date will be 45 WORKING days after today(), this function needs to exclude Saturdays, Sundays and any Public Holidays i.e. there could be either 10 or 12 weekend days added into the calculation depending on when today() is plus any additional Public Holidays.
I am using Excel 2003 although it will need to work in Excel 2010 shortly.
Is there a formula to calculate the working days left in the current month? I work in the financial services industry and am putting together a spreadsheet to automatically calculate sales production needs per "working day", based on my businesses schedule.
We are open M-F and Saturday, not including holidays. Now, I can look at a calendar, count the days left and put them in to the spreadsheet, but I'd prefer to have it calculate automatically.
On my spreadsheet, I have used the =TODAY() formula. In the cell below, I would like to have it calculate the actual working days remaining in the current month.
Since today is Sunday August 19th, I know that there are 11 working days left (starting Monday 08-20-2007). Is there a way to get Excel to do that?
