A Formula To Find Ages From DOB At Different Dates?
May 14, 2014
I am trying to find a formula to find out peoples ages from their date of birth but on different dates.I know you can do age from DOB 'today' or on a given date, but I have date of birth in column A and the date they were seen in column B and I would like their age in column C. Is this possible?
eg
Date of birth | Date seen | Age at date seen
01/05/1989 | 01/01/2011| ?
02/02/1956 | 02/03/2012| ?
05/02/1930 | 05/05/2009| ?
View 7 Replies
ADVERTISEMENT
Feb 19, 2014
I am finally at the end of this project. I need to find a code that will copy any lines that the person has turned 65 in the previous month or that will turn 65 in the current month to the Monthly tab.
Sample Macro jscalem.xlsx
View 3 Replies
View Related
Jan 22, 2014
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.
View 4 Replies
View Related
Nov 6, 2006
I have searched the forums to see if there is a similar prob already answered but to no avail (apologies if this has already been answered).
I am designing a sheet for a Uni project that needs to calculate the average attendance rates for students between age groups. My data has the age in a specific age for example:
Age
18
19
19
21
etc.
I am trying a SUMIF function that adds all attendance rates based on a criteria - however I can't get the criteria to fall between two ages ranges (more specifically, I need to sum the averages where the ages is between 21 - 25), as the function seems to only allow for one expression of criteria.
My function currently Reads:
=SUMIF(A1:A25, ">=21 & <=25", B1:B25)
Where A is the column recording ages and B is the column recording attendance rates. It presently returns 0.
Is there a better function for this task, or a way around the SUMIF criteria?
View 11 Replies
View Related
Apr 20, 2009
Need formula that will categorise ages. Column P contains the age of 4983 people. What I need to do is put these ages into categories in column Q:
0
1-4
5-9
10-14
15-19
20-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60-64
65-69
70-74
75-79
80-84
85-89
90-94
95-99
100-104
105-108
So, if the person's age is 86 in column P2, column Q2 should read 85-89.
View 9 Replies
View Related
Nov 4, 2008
I want vlookup to return results based on the *** and age of the person from tables in a separate sheet. For eg. if the person is "male" or "female" and if the person is "<=15", "16", or "17". I have been practicising with a formula that looks like this:
View 6 Replies
View Related
Feb 23, 2014
Here is my set up:
A2 to BF2 is a range of dates
A3 to BF3 are sales. Days without sales are 0.00
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
View 3 Replies
View Related
Aug 16, 2006
I have a masive table of dates (the date is created via a if formula)
what i need is so wheni enter 2 dates in 2 cells the system checks all the dates between the two specified and then returns the contents of them to a small area on the page.
Dates To Test 14/08/2026 19/08/2026
Results
14/08/2026
15/08/2026
16/08/2026
17/08/2026
18/08/2026
19/08/2026
Table..............
i dont mind using script or anything like that, i dont have much knowlage of it but do have coniderable understanding of other langages and usually figure it out :D
View 3 Replies
View Related
Dec 9, 2013
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
View 9 Replies
View Related
Dec 7, 2013
I want to numerically graph in a 2D stacked column graph the following:
Training Completed on "Time"
Training Completed but "Not on Time"
Training Not Completed
The training Due date was 10/30/2013.
Book1.xlsx
View 6 Replies
View Related
Mar 6, 2007
I have dates in column A (Source Dates) and Column C (Target Dates). All data is formatted as Dates. I want to find which dates in column A have a matching date in column C. When using the find statement within a For Each loop I can not find a date match unless I format the target dates as General. How can I use the Find Statement using dates without formating the target dates as General?
Option Explicit
Dim SourceDate As Range
Dim TargetDate As Range
Sub FindTargetDate()
Columns("C:C").Select
Selection.NumberFormat = "General"
For Each SourceDate In Range("A1:A32")
'MsgBox SourceDate
Set TargetDate = Sheet1.Range("C1:C7").Find(SourceDate, LookIn:=xlValues)
View 7 Replies
View Related
May 7, 2006
I am wondering if there is any way that I would be able to see when the last time an instance occurred within my Pick 3 lottery spreadsheet.
I don't know that I am stating this correctly, but here goes.
Pairs are located in columns F:K. Dates are in column A.
If I wanted to determine the last time that the 17 pair occurred, what formula would I use to give me the actual dates that this pair occurred on .
I need the dates listed so that I will then able to count the actual days in between hits.
Or would it be simplier to just try and use the filter feature of excel??
View 9 Replies
View Related
Nov 13, 2006
Is there a quick way to find weekend dates in excel? I tried pasting all 2006 dates into J2:J366 using the fill handle. It has weekdays as an option but not weekend dates. Any easy way (formula or simpler) to find the weekend dates in this range?
View 2 Replies
View Related
Jul 11, 2007
I have a list of employees and the dates on which they were absence from work for a set period (i.e. one month). Some employees have been off for a day, some for longer. This data has been pulled from a large datasheet and now needs formatting to add in the start and end dates of absence, ready to upload to a payroll system.
The Problem:
I have been able to get a macro working on a simple list of names, however the problem arrises where I have 2 employees off on the same day - I was using a loop to find the date last used, but this no longer works.
My Request:
A copy of the worksheet in question can be found here : http://www.carpe-luna.com/other/AbsenceQueryHelp.xls
But I'll try and describe the layout as much as possible
This is how my raw data is set up. (Up to row 50 but potentially more)
A.........B...........C..............D...........E.........F
EmpNo.....EmpName.....Start Date.....End Date....NoDays....NoHours
612.......Paul........26/01/07.............................12......
612.......Paul........27/01/07.............................12......
615.......Ian.........27/01/07.............................12......
758.......Peter.......16/01/07.............................12......
758.......Peter.......17/01/07.............................12......
758.......Peter.......18/01/07.............................12......
...........................................
View 9 Replies
View Related
Apr 21, 2008
I having trouble with a find function see attached sample. If I do it manually it will find it, but if try in a record macro i received a variable not set error.
Sub Macro3()
Sheets("Data-Donnees").Select
Range("A1").Select
Cells.Find(What:="04/07/2008", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub
View 5 Replies
View Related
Jun 17, 2014
i have column A and B with list of dates. Column A has earlier dates when compared to column B.
i am trying to find a formula with which i can know the difference between the dates in "number of months".
Ex: Column A has 06/01/2014 and Column B has 08/30/2014. The difference between the dates in terms of month is 3 which i need in a formula.
View 5 Replies
View Related
Apr 17, 2014
I have attached a workbook for example, the dates im looking to find difference between is marked in yellow...Need result in column E, would be nice with a formula you can just drag down...
Tried doing this myself, but the dates have weird formatting in these documents, so not sure how to deal with it.
View 9 Replies
View Related
Dec 1, 2009
I need to find ALL occurrences of a date on a worksheet, but could not find a macro on our forum.
I found this macro on the web.
View 6 Replies
View Related
Dec 2, 2009
I am trying to use the .Find method to match a date. In sheet2 I have a range of cells which are formatted to only show the day "dd" (I could format each cell to show (dd-mmm-yy, etc). In sheet1, I have dates which are formatted as "January 7, 2009"
I want to create a VBA find routine which allows me to match the date from Sheet2 (Ranged between cells A1:Z1) with Sheet1. Then output a MsgBox stating "Match found in cell $A$7 of Sheet1" I have tried a formula similar to:
PHP Set FoundCell = Sheets("Sheet1").Range("A1:Z1").Find _
(what:=DateValue("January 7, 2009") ,lookin:=xlFormulas)
View 5 Replies
View Related
Jun 12, 2014
I'm working on more code to take data from hotel reports, compile totals and transfer data to several other workbooks. In prior instances, I'd been working with data that had been converted from Adobe .pdf format, so while the cell values appeared to be dates, they actually were text. I'd come up with a solution to find the first of each month in a column of dates and insert a row at that point. However, I'm now working on this with a system that actually outputs in Excel format. As such, the date column is actually normal date serial value, rather than text and my code doesn't work. What can I do to modify this snippet of code to find the first of each month and insert a row when the values in the column are actual date serials?
Here's the current code:
With Sheets("Sheet1")
For RowToInsert = .UsedRange.Rows.Count To 14 Step -1
If (Range("A" & RowToInsert) Like "*/01/*") Then Rows(RowToInsert).EntireRow.Insert
If (Range("A" & RowToInsert) Like TodayVisualMatrix) Then
Rows(RowToInsert).EntireRow.Insert
Range("A" & RowToInsert).Value = "Subtotal"
End If
Next RowToInsert
End With
Row 3 doesn't find anything because while they appear in that format, the value of the cell isn't */01/*....
View 6 Replies
View Related
Aug 22, 2008
how to find the 2nd, 3rd and 4th date from list of dates.....
View 9 Replies
View Related
Feb 21, 2009
what is the formula to convert the date in MMYYYY into some numbers so that I can derive the difference between 2 dates?
For example:
I have 2 dates 31-Jan-09 and 28-Nov-05 in Column A & Column B respectively. I would like to derive no. of months between the difference of the 2 dates which in this case is 38 months.
View 9 Replies
View Related
Mar 13, 2009
I have two columns
Date Cab No
1 4501
2 4501
3 4501
5 4501
1 3603
2 3603
5 3603
The cab no 4501 is absent on 4th & cab 3603 is absent on 3rd and 4th i have more than four thousand entries with 50 cabs how to find with count if or any other formula the missing dates of the cabs
View 9 Replies
View Related
Jul 7, 2007
My source data generates a sheet full of individual dates and condo rates. We'll call it the "Rates Tab". The dates are not sorted or organized into a single column and to complicate it further the data has blank rows periodically. The only recognizable pattern is: (example) the date is in cell b12, then the condo rate will be 2 columns to the right in d12. On a separate sheet, the user will input a check in date(date A) and check out date(date b). I need to find the rates for all dates from Date A to Date B located on the Rates Tab. how to find a Date across various columns and then return a value 2 columns to the right from the "target"?
View 6 Replies
View Related
Jun 21, 2008
I have 3 sheets in my workbook. (List, Half 1, Half 2)
I am using the following code to search from a list of dates which is in the list sheet, and then find them on the other two sheets.
My code works fine until the 7th date, and then I get a runtime error 91.
Sub Find_Date()
Dim Variable, Variable2
Sheet1.Activate
Range("Y14").Select
Start:
Sheet1.Activate
ActiveCell.Offset(1, 0).Select
Do While ActiveCell.Value <> ""
Sheet1.Activate
Variable = ActiveCell.Value
Sheet5.Activate.....................
View 9 Replies
View Related
Dec 23, 2013
I try to find the price between two dates . I used lookup function, but couldn't get the correct result.
See detail at attached file and explained what exactly wanted : rates.xlsx‎
View 4 Replies
View Related
Aug 6, 2013
I am trying to capture the number of years/quarters are between two dates - giving credit for one day worked into the quarter.
In the attached png file you can see the formula I used for column J. This is the same formula I need to use for Column R but now using 10/1/11 and the date provided in Column R. The formula should be used in Column S "Part B" so that I can determine the number of years between 10/1/11 and the date in Column S.
View 7 Replies
View Related
Jul 31, 2007
I am working with a macro into which you input a date, the macro searches for that date (in column D of the data sheet), then copies any row with that date onto a new sheet.
This works great, but now I need to be able to search for more than one date at a time and return any rows that cantain ANY of those dates. For example, I would want to search for any row containing 01/01/07 - 01/07/07 or any row containing 01/01/07, 01/02/07, 01/03/07...01/07/07.
View 9 Replies
View Related
Feb 8, 2012
I have a formula to find the number of Mondays between 2 dates.
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(E2&":"&F2)),"DDD")="THU"))
I now need to add in the variable of holidays and other days off. This is where I am loss. I do have a list of the weekdays that we did have class and a list of when we didn't
View 9 Replies
View Related
Feb 11, 2013
How to find out the number of cells in a range containing dates less than a specified date.
View 4 Replies
View Related
