Hey folks,
Tried searching and couldn't find the answer I was looking for...
I have a date and time stamp which is system generated, I want to split these out so I can do analysis on date alone or time ect.
The current format is 05/01/2012 16:49..
Any ideas much appreciated, also, is it possible to use a formula that will tell me the week number??
Cheers
Dave
hello
=INT(A1)Format as a date
=MOD(A1,1)Format cell as time
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Hi
One way, could be this
1. =int(A1), format as date
2. =MOD(A1,1), format as time
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
week number with/out analysis tool pack installed
=INT((A1-DATE(YEAR(A1),1,1)-WEEKDAY(A1))/7)+2 - sunday first day
=INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4))/7)+2 - monday first day
with analysis tool pack use
=weeknum(A1)
Thanks folks, just found the weeknum tool there too!
Great site!
hi,
see if it helps you
http://www.vbaexpress.com/kb/getarticle.php?kb_id=538
Regards, John55
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
...enjoy -funny parrots-
your welcome from us.
@Fotis, i noticed that too.lol.let's split te price.
Assuming your timestamp is in column B and is in the format m/d/yyyy hh:mm:ss then procede as follows.
Add two column's (assuming that the timestamp's actually referencing the data you want to use).
In the now column C "=INT(B(row in question))
In column D "=B2-C2"
Format column C as date
Format column D as time.
Regards
Dougie
as shown above:
=INT(A1)Format as a date
=MOD(A1,1)Format cell as time
This method actually does not work for times ending in zero.
If the time is 7:40am using the mod(A1,1) fomula will give you 7:04am. Although as stated below in this thread pulling out the date with the Int(A1) and then subtracting it from the date/time variable does result in the correct time. But I had to figure this one out the hard way!
Last edited by RaeStat; 10-08-2014 at 03:09 PM. Reason: message wasn't linked to the initial post I was responding to
Raestat, thanks for the input, but this thread is over 2 years old, I doubt anyone is still following it.
and just FYI...
not so, I just tested it and it worked fine=MOD(A1,1)Format cell as time
This method actually does not work for times ending in zero.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks