I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.
=TRUNC((B1-A1)/365.25)&" Years "& ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"
Is there a way to show in excel that something feel within a specific time frame? An example being if I want to see the amount of time it took to close a customer service ticket. I would take the closed date minus the raised date, but then I would like to show in a cell it was open from Feb to Mar 2011.
I have two columns with dates (and times) in that I am trying to define how many days, hrs and mins have elapsed i.e. A1 has 12/12/06 21:00, B1 has 17/12/06 21:00. C1 has B1-A1 and is custom formatted to show as dd"days" hh"hrs" mm"mins". In this case it will therefore show as 5days 0hrs 0mins. Which is correct.
However, if more than 1month has elapsed then the format m"m" d"days" h"hrs" m"mins" does not work. For example 17/03/06 03:00 to 20/12/06 07:00 shows as 10m 4days 4hrs 00min, which it clearly isn't.
I know the reason it does this is because it calculates the difference between the two times and adds that to it's 0 value, which in my format is 01/01/1900 00:00. therefore when it adds 277days (the answer) it becomes 04/10/1900 04:00, so my formatting is just calling the month value ('10') and the day value ('4').
I understand the reason it does this, 277 days on from 01/01/1900 is indeed Oct 4th, but 277 days on from 17/03/06 is not 10months and 4 days as there are different length months in between. It also seems to add a month on, possibly because the format for 'months' is between 1 & 12 and therefore cannot begin at 0?
Does anyone know if it's possible to force excel to work out the correct number of months and days have elapsed between two dates and not apply it to 01/01/1900? Or any other possible solution, maybe with a different custom format?
See attached. (I added in some arrows for the first month so it is easier to follow the logic)
I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.
Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.
Essentially, I need the formula to return the correct amount that is being paid, even across different years.
I don't know if my formula can be modified or a new formula would be more efficient.
(This post originated from Commercial Services, which I just requested be removed from that forum)
ith the example below, i am having a problem with the calculation of Total Month. If i put Dec 31 2012 in A2 the total month is 10 BUT if I change the A2 to Dec 30, 2012 the total month is 11. What i am trying to do is to calculate the number of months only disregarding the years. example August 15, 2010 and January 16, 2013 i need to have total months of 5 months only.
I have a start date dd/mm/yyyy, and require a formula that everytime a spreadsheet is opened, based on the current date, to calculate how many years, months and days have elasped yy mm dd since the start date...
I would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.
Such as someone reaching 40 injury free years in June of this year I can let them know.
I am looking for a formula that will return the number of months (periods) between two dates, ignoring days and years. Using the above dates, which cannot be changed, the result needs to be 63. The formulas I have tried keep returning 64 because my later date is at the end of a period, and my earlier date is at the beginning.
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!
I have a file that uses dates, when a cell is in date the cell is "GREEN" but when the cell is out of date after 3 years it turns "RED". I want to add another formular so that when it gets to 2 years & 11 months the cell will go "YELLOW" for 1 month only before it turns "RED".
I have a sheet that is going to list usernames in a range of cells (e13:e19). Who gets listed there depends on another condition. So, all the cells could have a name, or only a couple of them could have a name. In another cell I have a formula that is to concatenate the results of who is in e13:e19 range, each name to have a comma between. The problem results when not all of the cells have a name .. the commas still appear ... so results could be ted, mary, bill,,,,bob, jeff ..etc.
I am using the formula below but it still allows the non-needed commas to appear: =MID(IF(ISTEXT(E13),","&E13,"") & IF(ISTEXT(E14),","&E14,"") & IF(ISTEXT(E15),","&E15,"") & IF(ISTEXT(E16),","&E16,"") & IF(ISTEXT(E17),","&E17,"") & IF(ISTEXT(E18),","&E18,"") & IF(ISTEXT(E19),","&E19,""),2,2000)