We are issued Sequence numbers at work for each incident in the following form.
NPS:2013:01:03:25
NPS = Agency
2013 = year
01 = month
03 = day
25 = sequential number resetting at the begining of each day.
I would like to pull the date out of this data so to put in a seperate field. I will then at that time use a formula to get the age of of the person from the date of the incident.
The date in MM/DD/YYYY is fine.
Any help would be appreciated.
Kevin
Hi kvnbdvs
Asumming in data is in A1, try:
Formula:
Please Login or Register to view this content.
Or
Formula:Please Login or Register to view this content.
Last edited by Kevin UK; 01-13-2013 at 03:33 AM.
Regards Kevin
Merged Cells (They are the work of the devil!!!)
Thats perfect!!
I copied the formula down the page but get #VALUE! in all the fields that aren't complete. Is it possible to make that go away?
thanks again
Hi
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
PERFECT again!!
One last thing.
I am then using =INT((F2-G2)/365.25) to get a persons age, but I get the same #VALUE! for the rest of the column.
I monkeyed around with the =IFERROR but had no luck figuring it out.
Thanks
Kevin
If
A1 = Date today
B1 = DOB
In C1: =(A1-B1)/365.25
That gives me age with decimal points, ie 23.4, 45.8 etc..
Hi
1: =ROUNDUP((A1-B1)/365.25,0)
2: =ROUNDDOWN((A1-B1)/365.25,0)
3: =DATEDIF(B1,A1,"y")
4: As post #6. Format cells > Number > 0 decimal places.
Last edited by Kevin UK; 01-13-2013 at 04:17 AM.
use round or format cell as - yy
either should work
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
the only thing is that I don't want it to round up.
Well ROUND IT DOWN or use the datedif or your formula in post #5
not sure how to do either of those.
Amazingly I figured it out with =IFERROR(ROUNDDOWN((F2-G2)/365.25,0)," ")
not too bad for a cop.
Internet forums and Youtube are power things!
Hang out here awhile, and you'll figure out ALL kinds of of amazing things
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks