to know the number of hours between A5 and J5 of my spreadsheet - working day is 8.30am to 5.30pm also need excluding weekends and bank holidays in the uk. I need the result to appeer in K5. the date and time format i have is: 2009-02-04 - 11:56:39 in column a5 and 2009-02-16 - 14:23:45 in column J5
I have over 100 of these calculations to do per month and it takes me ages manually.
I'm trying to Calculte the Averages of just the Teachers Salaries in my spreadsheet. If possible i would like to put the average at the end of each
of Teachers information.
Is there code that would allow me to do so.
I attached a sample file to view.
This is a two part question. I thank any help that can be given in regards to my problem. I have attached a spreadsheet similiar to what is used at work. We gather this information from a report we use.
Part one- in Column F, we have it set up to know how long the customer is deliquent. Column F is the difference between the date in column D and the date that is in cell E2. We are using the formula networkdays which will not count the saturday and sundays of the weeks.
The problem is, that sometimes when we place the information from the report, the value in column F is off a day, which causes us to have to adjust the formula so the information is the same. Why do we have to adjust the formula?
Part two - In the networkday formula, we do not want to include certain holidays, which is listed in column J. Is there a way to have the dates for the holidays auto advance if the dates in column J is less than the dates in column D?
I have a dataset storing 6 numbers in a row (where each number range from 1 to 50 and no duplication of number within each row). Totaling there is 1000 rows ....
View 9 Replies View RelatedI need to calculate an SLA.
I have 1 column called 'Date/Time Requested' containing the time and the date together, as in:
dd/mm/yy hh:00
I also have a second column called 'Date/Time Completed' containing the date and time in the same format as above.
I need a macro that compares the two and works out the following:
< 48 hours = "Within SLA"
> 48 hours = "Outside SLA"
I have two columns. The first column contains cells with multiple date values in text format (YYYY/MM/DD) in each cell. The other I was planning to be Calculated cells that I thought would compare the string of text (possibly converted to numbers) and determine which is the next in sequence after today's date. As there may be numbers in the past I cannot just use the first item.
The cell of text looks like this:2009-03-27 00:00:00
2009-04-10 00:00:00
2009-04-29 00:00:00
2009-05-13 00:00:00
2009-05-27 00:00:00
The calculated cell (based on today's date) should output:2009-04-10 00:00:00
Problem: I've been unable to figure out a calculation that will accomplish this. The idea I had was to examine the text in pieces and compare them numerically to "today" (as a number instead of a date).
I'm not very adept with Excel functions yet. I've started with this: "=(MID(A2,2,10))" but all it does is return the first item. I also tried using a Replace function to get rid of the dashes and also one that would return the dates as a decimal. But while I can get the numbers to change format, I don't know how to do the compare...
I need to calcuate how many times a date( any date for the month) aprears in the list. it needs to be calucated by each indivual month form a list that will span over a 12month period.
the problem i'm having is how do i get it to only read the month that i want?
I am starting a project that will involve finding how old people are using the difference of two years. Cell A1 will contain the function that automatically obtains the current year from the PC clock (or a manual input). Cells B1...B6 will contain text (names). Cells C1...C6 will contain the month of their birth. Cell D1...D6 will contain the day of their birth. And cells E1...E6 will contain the year of their birth. Cells F1...F6 will have the formula to calculate the difference between A1 and its respective cell column E.
The only functions I know are =(TODAY) which does not give me just the year. Also, I do not know how I would be able to do the calculation [e.i., =(TODAY) - 1988].
calculating project completion % based on Project start date and end dates .
View 5 Replies View RelatedI need to make a table for an injury category per shift per week. (Falls per shift per week)
I have attached an example of the spreadsheet. I have a formula in the table now that was calculating just the injury type per week but just need to add the function to read per shift but can't seem to get it to read correctly.
I have to calculate the flat revenue split by month between 2 different dates. The two dates may be in the same year or different years.
excel formula for the same.
Is it possible to do the same in pivot?
I'm trying to calculate shift working hours without using dates.
The scenario is
Cell A1 = Start Time
Cell A2 = Start Time
Cell A3 = Break Time
Basically I want the output to calculate hours worked between:-
0000 and 0600 as a total in cell A4
0600 and 1800 as a total in cell A5
1800 and 0000 as a total in cell A6
Then if the value of A5 is greater than A3, subtract A3, but if the value of A5 is less than the value of A3, A5 should be zero and the remainder of the value of A3 subtracted from A4 (or A6) depending which has a value.
I need to calculate time taken to fix a piece of equipment.
A1 B1
BREAK TIME FIX TIME
4/22/08 23:00 4/23/08 04:00
Should be 5 hours, but i can't find the formula to make it work.
I recently manage to create a spreadsheet. On the spreadsheet what I am looking to do is once I change the year in cell U1 from 2010 to 2011 to automatically change the days and the date number, and where Sat and Sun preferably to auto-fill in yellow the whole column within the table as you can see in the spreadsheet.if not then just do not display Sat/Sun columns at all..
View 5 Replies View RelatedI have put together the following code (with help from this and another forum):
View 5 Replies View RelatedI have employees that have different number of business days they work. I
need to be able to calculate when the employee has utilized a specific number
of business days specific to the days of the week they work and the number of
days per week work. For example, if I have an employee that works 3 business
days per week (Specifically M, W, and F), and I need to know the date this
employee worked a total of 30 business days, is there a way to calcuate this
date (which should be 6/9/06 if we use a start date of 4/3/06.
I need a formula that will allow me to put a date in cell a2, and in cell a3 put the the number of days between 2 dates. Example.. For example (A2) shows 08/06/08, a3 to show the number of days from 08/06/08 to 08/14/2008--(a3 )14 and (a4) to shows the number of days from 08/06/08-10/05/2008 ---(a4) 60 days.
View 5 Replies View RelatedI am trying to calculate dates that will happen 16 days from date specified in column A (A23:A200), based off a certain entry in Column B (B23:B200), which is named in cell P5. I can get the date by doing the specified date + 16, e.g. =IF($B$23:$B$200=$P$5,$A23+16,"")
The trouble I am running into is not having the date populate until it has reached 16 days from the dates in column A, show blank if possible until 16 days from date in column A. The dates in column A will always be previous to todays date.
So Column 1 I've got dates, need to sort through that and calculate Year-to-date and Month-to-Date values. These are both Sums of the cells....
YTD = Sum of all cells with most recent yr, in this case 2007
MTD = Sum of all cells in Column B for most recent month, Feb2007 here.
I've listed the desired solution for YTD and MTD on the sheet as well. (I'm guessing the solution will have something to do with SUMPRODUCT?)
I have a column CF which has to calculate the due date based on start date in column X and end date in column Y.
This is achievable however; I have dropdown columns in CE which has below option.
Below is the requirement.
If user select "Should Pay" from drop down in column CE, then in column CF the due date has to be Start date + 7 days
If user selects "Not to Pay" from drop down in column CE, then in column CF the due date has to be N/A or Blank
If user selects "Has Time to Pay" from drop down in column CE, then in column CF the due date has to be Start date + 9 days
If user selects "Misc" from drop down in column CE, then in column CF the due date has to be (End date - Start Date)/8+7
if this can be attainable either via formula or VBA.
How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8 - 17), and there is a weekend between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.
View 9 Replies View RelatedI have a static date and military time in B6 (5/10/07 18:00) I have to write various formulas to reflect 1 month (whatever calendar month it ends up in, so not necessarily 30 days) minus 7 days and then the same formula plus 7 days. How is this written?
View 9 Replies View RelatedI trying to find a formula to give the difference between two dates in year and month.
For example, the start date is Feb 1 2006 and end date is Jan 1 2008.
The formula should result in 2.11
I tried the following formula from a previous thread but it gives the answer in text but I need it to result in 2.11.
[url]
I would like to calculate the date but depends on how the information is scattered. In the first coloumn I have a set of dates, in the second coloumn I have a list of letters. I would like a macro that reads the the letters, and as they disappear calculate the time interval. For instance,
01/01/2002 B,C
02/01/2002 A,B,C
03/01/2002 C
So it firsts starts off and sees that the letters are B and C are there to begin with, then it moves on to the second row and then sees that the letter A has been added. Then to the third row and sees that there is only C so A lasted for 1 day (was there on 02/01/2002 and disappeared on 03/02/2002) and B lasted for 2 days (was there on 01/01/2002 and disappeared on 03/01/2002).
is there a way to calculate the number of days between two dates using the excel cells
example.....how do i put in formulas so excel will calulate the number of days between May 3rd and september 19?
i want to enter may 3 in say a1 then sept 19 in a2 a3 should says days between em
my spreadsheet is setup with two dates: A date for when someone joins our group A date for when they are released/leave
I want to be able to see exactly how many years/months/days transpired between these two dates. I have tried lots of different formulas with no success.
you must have the Analysis TookPack installed to use this function. It's comes with Excel. Look under Tools>Add Ins.
View 9 Replies View RelatedI have a column that contains dates for an event. I would like to tally quarterly and yearly totals for these dates. What formula can I use to accomplish this?
View 1 Replies View RelatedCalculating Number of Business Days Between Two Dates?
View 9 Replies View RelatedI have a spreadsheet where I have an intial date of issue of a warrant. Once this warrant has been executed, then a further column is populated with the date. The data I need to work out (Preferably without using macros if possible) is, if there is no execution date the length of time from issue to todays date, but if there is an execution date then length of time from issue to execution. I can do either on their own, but i would like to have only one column showing this info and if possible to update each time the sheet is opened
View 6 Replies View Related