 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Between date and time
How do you calculate two dates with hours, minutes and seconds included... Also, the data is all numbers like 20080514235050 to 20080515000505 (yyyymmddhhmmss)... Thanks.. You will pretty much need to parse the date out and then do your calculation. If your date is in E4 =DATE(LEFT(E4,4),MID(E4,5,2),MID(E4,7,2))+TIME(MID(E4,9,2),MID(E4,11,2),RIGHT(E4,2)) Will get your number into a date, then use it again on the other date an put the two together for the calculation so if one date was in E4 and the other in F4 and you wanted the difference =DATE(LEFT(E4,4),MID(E4,5,2),MID(E4,7,2))+TIME(MID(E4,9,2),MID(E4,11,2),RIGHT(E4,2))-=DATE(LEFT(F4,4),MID(F4,5,2),MID(F4,7,2))+TIME(MID(F4,9,2),MID(F4,11,2),RIGHT(F4,2)) This could be shortened up using a UDF Bear in mind your formatting of the result will determine how it is displayed. read up here for more info www.cpearson.com/excel/datetime.htm  SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk > How do you calculate two dates with hours, minutes and seconds included... > Also, the data is all numbers like 20080514235050 to 20080515000505 > (yyyymmddhhmmss)... Thanks.. You can convert to a TRUE date/time with this formula =TEXT(A1,"0000-00-00 00\:00\:00")+0 so, assuming data in A1 and B1 you can get the difference with this formula =TEXT(B1,"0000-00-00 00\:00\:00")-TEXT(A1,"0000-00-00 00\:00\:00") format result cell as [h]:mm:ss > You will pretty much need to parse the date out and then do your > calculation. If your date is in E4 [quoted text clipped - 17 lines] > > Also, the data is all numbers like 20080514235050 to 20080515000505 > > (yyyymmddhhmmss)... Thanks.. How could I read the result in seconds only? Thanks your help... >You can convert to a TRUE date/time with this formula > [quoted text clipped - 30 lines] >> > Also, the data is all numbers like 20080514235050 to 20080515000505 >> > (yyyymmddhhmmss)... Thanks.. You could use a custom number format of: [ss] And the value will still be a date/time. Or you could modify the formula to return the number of seconds: =(TEXT(B1,"0000-00-00 00\:00\:00")-TEXT(A1,"0000-00-00 00\:00\:00"))*24*60*60 (format as General) > How could I read the result in seconds only? Thanks your help... > [quoted text clipped - 41 lines] > >> > Also, the data is all numbers like 20080514235050 to 20080515000505 > >> > (yyyymmddhhmmss)... Thanks.. SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.