I am using the following formula:
=DATEDIF(F7,TODAY(),"Y")
I have a about 1200 rows with a column of student's birth dates. I need to convert the dates to ages and have them inputted in the next column Ages. I ran the code in the 'Age' column and it worked for the first birth date. But I do not know how to copy the code to the rest of the rows so it will convert every birth date in each row.
Is it possible to convert a students DoB into their term of birth (ToB).
I.e. a student has the DoB of 1/1/2001 which would mean their ToB would be 'Spring'
I suppose what I'm after is...
If a DoB falls into one of three groups
1/1/year to 30/4/year = 'Spring'
1/5/year to 31/8/year = 'Summer'
1/9/year to 31/12/year = 'Autumn'
The year of birth is irrelevant, it's the month which determines the 'term of birth' (Spring/Summer/Autumn).
I need a formula to calculate age today based on a person's date of birth. I used to know this but I have not used it for awhile.
View 4 Replies View Relatedhow to make a cell to change color oatomatically accordant to Year and Month reflexted already in the cell. I have a spreadsheet with Names,Surnames,DOB and also a column that is given the AGE years months and days.
If somebody reaches 21 -1 -2 -3 months this should show different colors in the persons row in a particular column. The function used for the AGE is fund on ozgrid.com: =DATEDIF(H4,TODAY(),"y")&" years "&DATEDIF(H4,TODAY(),"ym")&" months "&DATEDIF(H4,TODAY(),"md")&" days"
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
Future Date Sample.xlsx
I have attached a spreadsheet with a formula I'm looking for. I tried HLOOKUP, INDEX & MATCH, And others, but I was nowhere near what I needed.
I have a report that in column A shows a ship date. These ship dates may be "Previous","Same Day", "Next Day", or "Future".
I need VBA code that will look at the day in column A and determine if it matches the current date on the computer if it does then in column W in the same row I need the string "Same Day".
If its the systems date plus 1 day column W in the same row would state "Next day".
If its the systems date plus 2 or more column W in the same row would state "Future".
If its any date prior than than the current system date then column W in the same row would state "Previous".
I am having a problem which I imagine will be fairly simple to resolve but I can't figure it out!
Cell A1 contains a Date eg (01/01/07)
In Cell A2 I want to write a formula that says: Insert 'Warranty' if the system date is within 365 days of the date in Cell A1.
I have got a database wherein it contains the details like Employee Name, Birth Date, Date of Joining, etc in Sheet1. Suppose, Employee AAA birth date is 30/Jul/1980. When on 30th July 2009, it should display the Employee Id in Cell C3.
How should I lookup for the current date & month from the Employee Birth Date?
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 am building a spreadsheet that has everyone's birth date on it. If the person is not 22 years of age or older I would like for the cell text containing their birth date to be highlighted in RED.
View 9 Replies View RelatedI have the date of death and the age at death in Years, Month, Days. What formula would I use to figure the date of birth?
View 9 Replies View RelatedI need to work out how to get the last (or most recent) Friday date. Suppose today is 14/02/2013, then the previous date is: 08/02/2013. If today is 15/02/2013, then I just want it to be 15/02/2013 as it is Friday.
View 3 Replies View RelatedI'm trying to use a list of date of births to determine an age output.
However the date of births are all in a simple reverse formate, eg
19790822 would be 22nd August 1979
Ideally, i would take this number and the output would just be the persons age in whole years.
I am using Office 2007 - Excel
I have a list of students with associated Schools
For each High School -Based on students Date of Birth (DOB) I am trying to get a count for:
Students UNDER 16 DOB before 8/25/91 and for each high school students over 16 DOB after 8/25/91
I thought subtototals, countif , maybe a pivot table to break down by school then do the 2 counts -
but I am at a loss for a formula to do the count
I have attached a sample file but the actual file has thousands of students and 23 different High schools which is why I was trying for subtotals or Pivot
I am trying to get a function to return a date based on the string in the preceding cell. If the cell, say A1, has the string "Before 12:30", i want the cell B1 to put the date 5 days from now in the cell. If there is any other string entered then 6 days from now. I also need it to include the networkdays function.
Here's what i've got, messy i know but functions aren't my strong point. Also would i save file size if i did this in VBA? ....
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 RelatedI have tried to record a macro and change it to what I need but so far I have not been able to make it work.
I would like to have a macro that will auto filter on colomn 1 in the following way.........BETWEEN >= Today -10days and Today.
I have the formula for calculating a 13 week rolling average, but I need to be able to set goals in the future and still have the average calculate from current and not from future.
I have my dates set up in row 3 (these dates are all mondays, not every day) and my data in row 4. I need the 13 week rolling average to calculate starting from the prior monday and going back 12 more mondays for the 13 weeks. IE: This monday I'm running reports that the info will be entered into the previous week's monday.
Here is the formula that I'm currently using: =AVERAGE(OFFSET(K4,,COUNT(K4:BV4)-13,,13))
The formula works but will calculate the average for 13 weeks prior based on the last cell with data in it. It's calculating the average of the last 13 weeks of next year.
how to make this work without having to remember my weekly goals every week??
I'm trying to find a formula that will allow me to show a value based on whether certain dates have passed. This is for the purposes of calculating PTO hours and removing unpaid holiday hours from the equation.
I've got "slave" cells set up with the dates and amount of hours for each holiday, but I'm having some trouble.
I've been trying something to the effect of:
=IF(B4>B14,8 IF(B4>B15, 13))
Now, I can get it to show the "8" if I keep only the first IF function, but adding additional F functions like in the formula above only returns errors.
My goal is to get B7 to show the sum (through formula or manual entry) of C14-C21 based on whether B4 [=NOW()] is before or after the corresponding date in B14-B21.
I need to clarify any terminology or other cell values.
how do you calculate someone's age nearest today
View 3 Replies View RelatedI'm trying to link three cells lets call them C7, E7, and G7 to an NBA schedule. I'd like to have C7 Display the visiting team, E7 to display the date, and G7 to display the time of the game. Ideally I'd like these cells to change after the date of the previous game has passed to the next game. Is this possible? Would I need to create a separate sheet with the Visiting team, date of game, and tiem of game?
View 3 Replies View RelatedI need to find out over aged students by date of birth - now()-age started to school: DOB 8/16/2000 , Now() Age started school is 8/30/2005. Student is in grade 1. should be age 6, however, he is 8. Answer should be a little over 2 yrs.
View 2 Replies View RelatedSheet1 is a report that expresses 90 days of information and Sheet2 has three columns of 365 day information. Sheet1 has a title, a today function for the date (cell B2), two columns (A5:A94 and B5:B94), and then some other info not relevant to the problem. Sheet2 has three columns (A1:A365, B1:B365, and C1:C365) with the date in column A starting at the first of the year and then two lists of information in columns B and C. I am wondering if there is any way excel would be able to recognize the Date (B2) on Sheet1 and be able to pull the information from Sheet2 starting on that date for the next 90 days.
For example:
My report always starts on the day I am accessing it. So... Say it is 1/15/13. My today function in Sheet1 (B2) would express 1/15/13. This date would be equal to A15 on Sheet2. So I would want A5 & B5 on Sheet1 to return B15 & C15 from Sheet2 respectively.
A5 Sheet1 = B15 Sheet2 & B5 Sheet1 = C15 Sheet2
A6 Sheet1 = B16 Sheet2 & B6 Sheet1 = C16 Sheet2
...
A93 Sheet1 = B103 Sheet2 & B93 Sheet1 = C103 Sheet2
A94 Sheet1 = B104 Sheet2 & B94 Sheet1 = C104 Sheet2
With that being said, the next time I open up my report, say a week from the 15th or 1/22/13 (expressed by my today function in B2), I would want A5:A94 & B5:B94 to reference the new set of information:
A5 Sheet1 = B22 Sheet2 & B5 Sheet1 = C22 Sheet2
A6 Sheet1 = B23 Sheet2 & B6 Sheet1 = C23 Sheet2
...
A93 Sheet1 = B110 Sheet2 & B93 Sheet1 = C110 Sheet2
A94 Sheet1 = B111 Sheet2 & B94 Sheet1 = C111 Sheet2
Is this at all possible?
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.
I have a list of birthdays (in date-month-year format) and simply want to highlight them if the date and month are past today's date.
View 9 Replies View RelatedI am trying to write a formula that will have the cell display todays date if it is after 2:00PM, and if it is before 2:00PM I need the cell to display Today()-1. I have tried several iterations and I'm stumped.
View 5 Replies View Relatedi have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies View Related