In B13 I have the start date: 12/1/2008
In C13 I have the end date: 1/5/2009
In cell D13 I want a formula that counts the number of days between the two dates that are not Sunday. The start and end dates are included in the count.
I have created this formula using Ctrl + Shift + Enter:
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
Need a formula (E1) to determine the number of days that have passed between two given dates (C1 & D1) ONLY when the Status of a given report (B1) is "Late"
B1 = Status (Pending; On Hold; Late; Completed) (i.e "LATE") C1 = Status Date (i.e. 28 Mar 14) D1 = Deadline (date) (i.e. 25 Mar 14) E1 = Total Days Late (= ?)
I need to figure out how to tell Excel to count the number of invoices on a worksheet older than 30 days and display that number in my Summary worksheet. Here's a quick example:
Worksheet '1234' has 10 invoices, 6 of which are more than 30 days old (invoice dates are in column B). Worksheet 'Summary' needs to display the total number of invoices and the number older than 30 days.
I am trying to set up a sick leave list which will work out the cumulative total for the last 12 months (from last day of last sick entry). The problem I have, as the is is added to, I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to the last date. Hope this is clear. The next problem is I need it to work out total days and then without weekends. Below is the chart I have to work out.
Column 1 Start date of sickness column 2 End date Column 3 the number of days sick in period (column 2 less column 1 in days) column 4 the number of days sick in period above excluding week ends (column 2 less column 1, less weekends in days) column 5 total sick days in last 12 months from date in column 2 going back a year including weekends column 6 total sick days in last 12 months from date in column 2 going back a year excluding weekends
What function or formulas can I use to calculate column 3,4,5 & 6. I am a not very experienecd in Excel
Now I need to find no. of days which fall under the month of FEB from the date range 21-Feb-2013 till 07-Mar-2013 (Col C2, Col D2) which are working days. so that I can subtract that from no. of working days for that month (FEB) to get the no. of working days which the person has actually worked.
I have been using a formula to count the number of days between 2 dates and if the date was older than 12 months it would work fine.
The data that i was using has been changed and they have decided to change the format of the dates and dropped the year reference no all i have to work with is dates like 27-07 this represents 27th July i can format this date to work fine unless the date falls before 1 jan this year all i get is lots of numbers and it stuffs up the whole calculation.
I m trying to do is this. For every person in company there is a table 30 days (cells) long, and 3 shifts (cells) wide. Every shift leader fills in the data in the "calendar" so every day, for every person, he will fill in number of hours.
I have to make excel process the whole calendar 30 x 3 = 90 cells, into final results for our payroll department. So the end product of the excel table should be Person name, total work days, total sick leave days, holiday days, and so on.
specific problem i have is how to calculate number of State Holiday days. You can see my best attempt in the attached file.
if there is only one number / mark entered each day (in only one of the 3 shifts) thansumproduct works ok.
I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.
We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.
Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!
I have three companies in column A, In Column B I have a date of when an item was sent to them, in Column C I have the amount of days this item has been out with that company. Is there a formula that will count the number of days an item has been out with one of the companies per month, and find an average if more than 1 item has been sent per month. Two items have been sent to company A in Sep 09, one item has been there 10 days the other 20 days, the answer I would be looking for is 15.
I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. Any way to identify the number of days, per calender month, that falls in a date range.
sample data...
Start Date End Date Old Value New Value
08/03/2010 18/06/2010 16758.2 16758.1
[Code] .......
I need to break down the total number of days per month
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
24 30 31 18
[Code] ......
As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...
I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.
(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it).
** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key then hit ENTER.
The logic of the formula is:
The max Sunday date within the date range minus the min Monday date within the date range +1 = total days divided by 7 (days in a full week) = full weeks
There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.
I need a formula to count how many days hours & mins between 2 dates but included in the formula need to ensure it only counts business days.
Column A 02/09/2011 13:00
Column AO 02/09/2011 13:02
I need in Column AP to show 0 00:02
The formula I have tried using is - =IF(AO3="","",AO3-A3) which doesnt work as instead of showing 0 00:02 it shows 1 00:02. I dont want it to count as 1 day just 2 mins.
Once that has been worked out I need to include WORKDAYS formula to only count working days. I have created the list on 'Controls' Tab Column A10:A47.
I am trying to update a spreadsheet for 2012 to 2013 and want to use a formula to change SUNDAY 8 January to Sunday 9 January and so on for rest of year.
I want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2. If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2. If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2. If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2. If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
I have a spreadsheet with a list in column A, and starting from column D in row 1 are daily dates e.g. 01/10/06, 02/10/06, 03/10/06. On a daily basis an x is added manually to the spreadsheet where the list criteria matches the specific date e.g
A B C D E F 1 01/10/06 02/10/06 03/10/06 2 M123 x x 3 M456 x x 4 M789 x x
What i would like to do in column C is to measure how many x's are in the last 10 days for each row. I have used formulas in the past to do something similar but I can't find them. Sorry I can't upload a sample but firewall won't allow.
I have the vacation sheet so I can track my vacation hours used and available. The last thing I need is a way to count the number of days I'm not at my site. It doesn't matter why (Training, Holiday, Travel or Vacation) I need to track total days of site.
I would like to have a formula in a spreadsheet of personal bills that counts down the number of days until each one is due each month.
In one column, I have a digit in each cell to represent the day of the month each month that the bill is due. Someone helped me with a formula that can count down days to a date that is a higher number than the present day of the month, but can I get a formula to count the days from, say, December (or any month) 28th to a due date of the 6th (of the next month)?
