I have some cells which must be in the format 15/06/2007 15:25
I then need to add either days, months or years onto it.
Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00
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...
My date formula knowledge is a little limited.
How can I convert the number of days for example 366 days to years, months, days 366=1year, 0 months, 1 day
View 3 Replies View Relatedmy 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.
I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"
C7 3/24/04
D7 1/4/08
What is returned is 3 years, 9 months, 136 days
How might if fix it show the correct days?
Cell A1 contains 02/29/2012
Cell B1 contains 12/01/2006
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 date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.
View 9 Replies View RelatedI am trying to count the number of orders written per day. I used the following formula on another sheet and it worked fine, however on this one, A8 is a date only, and F2:F2000 is a date and a time.
I tried reformating the cell for date only, and it displays only the date, however, the time information is still stored, and I can't get it to do the count. Is there a way to count the date regardless of the time?
=COUNTIF('ACT Legacy'!$F$2:$F$2000,Stats!A8)
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'm looking for a formula that will return the time left to a birthday, in Month and Days.
View 5 Replies View RelatedI use [=IF(F15="","",EDATE(F15,-D15))] to track time frames. The problem is Edate works great for months but on occasion I need days, like 40 days. In cell D15 I have a list where the user must choose from, but one of the choices needs to be in days so the Edate won’t work anymore.
View 5 Replies View RelatedI am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.
View 9 Replies View RelatedI've been racking my brain on this one for awhile, and I feel like I'm going the right direction but...
I have a list of dates like:
11/30/2007
10/31/2007
9/30/2007
8/31/2007
7/31/2007
6/30/2007
5/31/2007
or maybe
11/30/2007
11/23/2007
11/16/2007
11/9/2007
11/2/2007
10/26/2007
10/19/2007
10/12/2007
10/5/2007
9/28/2007
9/21/2007
9/14/2007
9/7/2007
8/28/2007
...and I need to determine if the difference between them is daily, weekly, or monthly. There are 79 of these lists, on 79 different sheets so I can't visually peg them as daily, weekly, or monthly.
The way I have been looking at the problem is to calculate the number of months between the last & first dates and see if the resulting answer (+1) matches the number of actual dates, and so on...
I have a date stored in a variable and I need to add month(s) to that date. For example if I have 10/1/2013 in a variable if I add 1 to that month the result should be 11/1/2013. I am using the following formula but the problem is in the result date format, which shows as 1/31/1990, if I add 2 to the original date, the result is 2/29/1900 which ideally should have been 12/1/2013.
My formula is :
ActiveCell.Value = "=date(year(" & last_date & "),month(" & last_date & ")+" & i & ",min(day(" & last_date & "),day(date(year(" & last_date & "),month(" & last_date & ")+" & i & "+1,0))))"
Any other formula to add months to a given date?
Is there a way to add # of months to a date and then find out what would be the exact date?
For example if cell A1 = 04/30/07 and Cell B1 = 14 what is the statement that would give me 06/30/08 in Cell C1?
Also, how can I modify the statement if Cell B1 = 14.5 to get 07/15/08?
I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.
View 2 Replies View RelatedThree columns.
A - Date last checked
B - Due Date
C - Actual Date checked
Currently column B simply has =A+84 and will display a date 3 months in future.. Great..
However now i want column B to still display the 3 month date - unless there is a date in C, whereby i want B to then display OK. SO what im sort of needing is =A+84 (or if somethings in C then "OK")
I'm in Excel 2010, and the cell with the date I want to work from is H22.
I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
I should also probably note that the date in H22 is the result of another function.
=EDATE(G22,12)
I want to put a date in one cell then in the next cell i want it to add 60 days automatically, so to give the new date.
View 4 Replies View RelatedI 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".
View 3 Replies View RelatedI have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
Basically it's to make my content management easier.
I have made a button and assigned a macro to it. At the moment the button creates a new column and makes it the right size etc.
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...
View 3 Replies View RelatedThe issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference
View 2 Replies View RelatedPlease refer to the attached screenshot of my working spreadsheet.
I'm attempting to concatenate lines 86 and 87, month and year together.
The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"
I have been trying to figure out a formula to convert the amount of months in a certain cell to years.
I have come up with:
=INT(E29/12)&" years "&MOD(E29,12) & " months"
The problem is it comes up with 2 years 3.37063951499227 months
How I change it to come up with 2.4 months, round it to the highest number instead of loads of decimal places? I have tried change cell E29 do 0 decimal places but it doesn't work.
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"
Which formula should I use to return years and months between two dates.
4/1/05 7/30/25
I am looking for a formula that looks at a date and could add business days to it.
for example:
If the date in a field is Monday, 15-dec-08 and my formula is to add 5 Business days (mon-fri) to it not including that date. The desired result would be Monday, 22-dec-08 but my formula gives me 20-dec-08
I have a date in A2 7th Nov 2011
Column B is number weeks until next appointment - 4.
Column C is the answer Friday 2nd December if the normal result is a Saturday or Sunday then use Friday.