At the moment I have a simple formula that calculates the due date of a task
The log in date is in cell B3, I use a simple formula in cell F3 "=B3+20" - This gives me the due date
I now need to add another log in date but use the same due date cell
So now ....
Either B3 or D3 will contain a date - whichever one is populated I will need the due date displayed in cell F3
how to calculate the age from the date of birth and was given these two responses
1) I may be way off here but all that coding seems a waste !
I have in cell A1 = BLANK <--- enter your DOB here
I have in cell A2 =NOW()
in cell A3 do = A2-A1
Format that to General
In Cell A4 do =A3/365
Format that to no deciamal places and you get the age - this one is adding a year to everyones age?
2) Alternatively, if the DOB is A1 :-
=INT(YEARFRAC(A1,TODAY(),1)) - This one does not work it is saying there is an error with the "yearfrac"?
It is also when I am trying to put these into a pivot table, the pivot table is listing these down to 5 decimal places( when I have formatted to zero)????
http://www.cpearson.com/excel/datedif.htm
--
"malvis" wrote:
> Is there a formula that can calculate age when given only Date of Birth?
I have a need to calculate the latest and earliest date in a given range, but I cannot seem to make it work properly. The range looks like this:
Code:
23/08/201120/08/2011 13/08/11 13/08/11 13/08/11 13/08/11
and the code is:
Code:
d1ST = Application.WorksheetFunction.Max(rRow)
No matter the information in the range, the statement is returning "0" (or "12:00:00") - I have tried setting d1ST as a date and double data type.
I think I may have over engineered this formula.
$F$40 = Cell with Event Date
=IF(WEEKDAY($F$40,2)
I have a list of several hundred loan recipients who are all starting to pay their loans back on different dates. I am able to get the calculation to work so that I just need to put in the start date and excel works out the other 5 or 6 payment dates.The problem is, some people pay back on the 29th, 30th of the month which is giving me a headache when they are supposed to pay in February. The formula I am using calculates the next date of payment to be on the 3rd March (skipping the February payment all together).
The formula I am using is : =DATE(YEAR(A10),MONTH(A10)+1,DAY(A10))
I am trying to create a formula that returns the date on the friday (ie last working day) of whatever week I am currently in. So, for example, I guess I would use the 'TODAY' or 'NOW' function somewhere to determine the present date - but how do I then determine the date on the Friday of the current week?
View 9 Replies View RelatedI was trying to figure out how you could caluculate a project end date, based on a start date (dd,mmm,yyyy) and a project duration of lets say 5 work day and the work week is five days long (monday to friday). If i have a leave on the week days like tuesday is bank holiday then it should give be the date excluding that tuesday and wednesday leave and caluculate 5 working days and shoule give the project end date.
Example date: 5-Oct-09
Project end date:09-Oct-09
If i have bank leave on 06-Oct-09 means.
My project end date:12-Oct-09
Note:Bank holidays are fixed date example(every year bank holiday 06-oct-09,02-Dec-09,25-Dec-09)
Please dont need a macro.
I have dates listed in column A indicating the day an action was performed. In column B I need to have the date in column A changed to a Friday. If the date in column A is not a Friday, then the date in column B needs to fall back to the previous Friday. For example, if the date entered in column A is today, Monday August 7, then the date in column B that I would be Friday August 4. Can anyone think of code that determine this and place the value in column B? Currently I use vlookup to go to another sheet where all the possible dates are listed with their respective Fridays, but this slows down the calculation worksheet as the file is quite large.
View 9 Replies View RelatedI am trying to determine a formula that will tell me what the date will be 61 days and 90 days from a selected date. For example:
A1: 04/11/2014
A2: (Date 61 days from date in A1)
A3: (Date 90 days from date in A1)
Is this possible??
I have a contract start date. The payment is due every seven days. I have entered the start date. i need the "next due date" to automatically calculate in the "due date column". here is my problem if a customer has a due date say on 2/15/2014 his next due date should be 2/22/2014. regardless if he pays on the 14th or 21st. i need this date to change each time a payment is made.
View 7 Replies View RelatedI have a truck log. Every time some comes in to take a truck they must log the truck out with the millage. After a week, I am supposed to find out how many miles my trucks ran for that week.
What would be the code to....
In K column, calculate the milage that is in row F; which is A3 - A2 and so on down. But it would have to skip a line every time the truck number changes (in column C) it would skip that line. It would go until a blank cell.
Does anyone have any ideas how to do that?
Within 4 columns, I have a series of:
Start Dates / Start Times / End Dates / End Times
I need to create a formula which will give me the sum total of the difference between the data sets.
For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours.
I need a formula which will calculate this for me.
I'm using the following formula to find the 1st date that a particular rep wrote an order.
{=IF(S4="","",IF(MINA(IF('Daily Compliance'!$A$2:$A$5028=B4,'Daily Compliance'!$O$2:$O$5028))=0,"",MINA(IF('Daily Compliance'!$A$2:$A$5028=B4,'Daily Compliance'!$O$2:$O$5028))))}
I also need to figure out when they write their 10th order, but have had no success figuring that out.
I 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.
I am trining to calculate SLA times based on P2, P3 & P4 calls, the only issue is with P2's as the SLA includes the weekend, how to include the weekends into my formula?
AE2 = Priority
U2 = START DATE & TIME
V2 = END DATE & TIME
[Code]....
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
I've managed to sort the first half of this issue, in that I can calculate the number of days within a given month which fall inside specified to and from date parameters..
The formula I've used is thus :
Code:
=IF(EOMONTH(A13,0)EOMONTH($I$9,0),0,IF(EOMONTH(A13,0)>$I$9,$I$9-(EOMONTH($I$9,-1)),EOMONTH(A13,0)-(EOMONTH(A13,-1))))))
This examines a date value (A13 in this case) and assess whether any days within that month are between the two date parameters specified in H9 and I9.
H9 = 01/01/2011
I9 = 15/05/2011
Jan-11
31
[Code]....
What I need to do now is specify a second set of date parameters in H10 and I10 and add those into the assessment. The dates will NOT overlap.
So, for example, if I were to set H10 as 01/07/2011 and I10 as 15/07/2011, the results would appear as below
Jan-11
31
Feb-11
28
[Code].....
I am looking for a formula that calculates an individuals exact age given their date of birth. That shows just the age in years.
View 9 Replies View RelatedCode:.....
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:
Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.
Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.
The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.
So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.
I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.
I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.
Start/Finish
21/12/09 07:00 to21/12/09 11:09
21/12/09 07:46 to21/12/09 14:41
21/12/09 12:13 to21/12/09 22:08
21/12/09 16:40 to21/12/09 18:05
21/12/09 19:40 to22/12/09 02:34
21/12/09 23:20 to22/12/09 04:39
22/12/09 02:06 to22/12/09 06:15
I'm having trouble with a complex Excel formula. I'm calculating child support owed based on the current date coupled with future child support owed. My columns are as follows:
Debt / Future Amount Owed / Monthly Payment / Total Amount Paid
10,148.72 / 83312 / 508 / 1000
On the first of each new month, the Future Amount Owed should decrease by 508. Altering the Total Amount Paid should decrease the Debt column. Progressing another month without altering the Total Amount Paid column should add 508 to the Debt column while subtracting 508 from the Future Amount Owed column.
Instead of calculating a time difference manually I want to have a formula do it for me. What I want to achieve goes as followed:
08/06/2006 04:33
12/06/2006 01:05
Time difference is 92:32 [hh:mm]
another example:
09/06/2006 12:42
12/06/2006 11:35
Time difference is 70:53 [hh:mm]
So instead of getting 92:32 and 70:53 by calculating it myself I would like to have a formula do it for me. Otherwise I'll have to invest a lot of time to get the information I need.
I have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
I am putting together a simple table to display current week's data vs previous weeks. The current week's data is drawn from a status chart which changes frequently. The constant change is fine for 'Current' as I only want the current data displayed.
The problem I am having is calculating the number of late jobs that existed during the previous week.
The status log has a due date which is compared to the current date to determine 'on time' status for the current week.
Due dates are reissued regularly so I can't use
=COUNTIF(RANGE,WEEKNUM(NOW()-1)) to return data about last week from my status chart.
I have available a 'Movement Log' (in the workbook but a separate worksheet) which tracks the changes in the due date field, but I'm not sure how to integrate that data to calculate the # of jobs that were running late from the last week.
My thought is that I need to perform a count of the # of late based on a comparison of 'due date' to 'date of the last day of last week' with a way to insert the "old due date" from the movement log to replace what is shown in the status log if necessary.
Movement Log.JPG
Status Chart.JPG
I am having difficulty calculating the number of total overlapping days between several date ranges (6 of them to be precise)
I am using Excel 2003 and my data looks like this:
Sent Out (A) - Received (B)
01/06/2010 13:00 - 30/06/2010 13:00
02/06/2010 13:00 - 16/06/2010 13:00
09/06/2010 13:00 - 10/06/2010 13:00
21/06/2010 13:00 - 25/06/2010 9:44
23/06/2010 13:00 - 25/06/2010 10:56
23/06/2010 13:00 - 29/06/2010 13:00
I really can't wrap my head around the idea of calculating overlap (in network days) between six date ranges and was wondering if any of you would have a solution to this problem.
I am having a problem with some of the cells in a spreadsheet not calculating.
For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is).
I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?
I have the workbook set to Auto Calc the Cells, which seems to be a bad idea, because with the amount of formula in the workbook, it slows the whole thing down and makes it impossible to do anything within it.
Is there a way that I can speed this process up, using some code when I run certain sections?
There is a copy of what I am doing, so you can see the amount of formula.
I am having difficulty calculating the number of total overlapping days between several date ranges for each item in another sheet which has unique items
I am using Excel 2003 and my data looks like this:
Sheet-1 Sheet-2
Item - Sent Out (A) - Received (B) Unique Item Number of days excluding overlap days
1234 01/06/2010 - 30/06/2010 1234 -
4321 02/06/2010 - 16/06/2010 4321 -
1234 09/06/2010 - 10/06/2010
4321 21/06/2010 - 25/06/2010
1234 23/06/2010 - 25/06/2010
4321 23/06/2010 - 29/06/2010
I have used the below formula found from the earlier post but need to add a condition calculating the days for each item.
=SUM(IF(MMULT((NETWORKDAYS(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7)))),2)<6)
*(ROW(INDIRECT(MIN( INT(A2:A7))&":"&MAX(INT(B2:B7))))>=TRANSPOSE(INT(A2:A7)))
*(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7))))<=TRANSPOSE(B2:B7))+0,ROW(A2:A7)^0),1))