Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-12-2013, 01:00 PM
alysolyman alysolyman is offline Calculate difference by days and hours Windows 7 32bit Calculate difference by days and hours Office 2003
Novice
Calculate difference by days and hours
 
Join Date: Feb 2013
Posts: 12
alysolyman is on a distinguished road
Default Calculate difference by days and hours


I receive some requests from my clients. I need to register the date and time of receiving and upon delivering the order, I need to register the date and time for delivery and in the next column I need to calculate the duration for accomplishment (= Delivery date and time - receiving date and time) so that the result will be 3 (days) and (50) minutes for examples

Kindly help!
Reply With Quote
  #2  
Old 05-12-2013, 07:14 PM
macropod's Avatar
macropod macropod is offline Calculate difference by days and hours Windows 7 64bit Calculate difference by days and hours Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

For periods less than a month, Excel can do this automatically without the need for macros, via a simple subtraction formula and a custom cell format (d h:mm:ss). For greater periods, a different formula can be used.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-12-2013, 09:13 PM
alysolyman alysolyman is offline Calculate difference by days and hours Windows 7 32bit Calculate difference by days and hours Office 2003
Novice
Calculate difference by days and hours
 
Join Date: Feb 2013
Posts: 12
alysolyman is on a distinguished road
Default

attached a sample, it's not giving the result I need
Attached Files
File Type: xlsx try.xlsx (9.5 KB, 18 views)
Reply With Quote
  #4  
Old 05-12-2013, 09:38 PM
macropod's Avatar
macropod macropod is offline Calculate difference by days and hours Windows 7 64bit Calculate difference by days and hours Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The formula is working OK, but you haven't applied the custom format (d h:mm:ss) needed to turn it back into a normal day/time representation.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-13-2013, 02:07 AM
alysolyman alysolyman is offline Calculate difference by days and hours Windows 7 32bit Calculate difference by days and hours Office 2003
Novice
Calculate difference by days and hours
 
Join Date: Feb 2013
Posts: 12
alysolyman is on a distinguished road
Default

I've tried it. It's fine. Thanks.
what if the period exceeds one month?
Reply With Quote
  #6  
Old 05-13-2013, 02:14 AM
macropod's Avatar
macropod macropod is offline Calculate difference by days and hours Windows 7 64bit Calculate difference by days and hours Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As I said, you'd need a formula; with custom formatting, Excel can show months as well but, just like real months, they'll have variable day counts, depending on how many days there are in (IIRC) the current month. A formula might be:
=INT(A1-A2)&" "&TEXT(MOD((A1-A2),1),"hh:mm:ss")
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 05-13-2013, 02:19 AM
alysolyman alysolyman is offline Calculate difference by days and hours Windows 7 32bit Calculate difference by days and hours Office 2003
Novice
Calculate difference by days and hours
 
Join Date: Feb 2013
Posts: 12
alysolyman is on a distinguished road
Default

Thanks again. issue resolved
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate difference by days and hours Compare columns + calculate difference Inatic Excel 1 01-27-2013 08:00 AM
Calculate difference by days and hours Insert picture options, what's the difference? Jennifer Murphy Drawing and Graphics 3 10-09-2012 05:12 AM
Calculate difference by days and hours 5 days/week during planning & 7 days/week during implementation sanlen Project 1 06-25-2012 04:17 PM
Calculate difference by days and hours What is the difference between the category and the distribution list? Jamal NUMAN Outlook 1 11-21-2010 09:31 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:43 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft