Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-19-2013, 06:45 AM
funkyfido funkyfido is offline Formula to calculate Date and Time Windows XP Formula to calculate Date and Time Office 2007
Novice
Formula to calculate Date and Time
 
Join Date: Jun 2013
Posts: 24
funkyfido is on a distinguished road
Default Formula to calculate Date and Time

I am currently using Excel 2007

In Cell A1 I have a start date of 14/6/13 09:01
In cell B1 I have an end date of 19/6/13 14:37
I have the following formula to calculate Hours and minutes
=INT(B1-A1)&TEXT(B1-A1,":hh:mm")

In Cell C1 i have another start date of 19/6/13 15:00
In cell D1 I have another end date of 20/6/13 17:32


I have another formula as above to calculate the hours and minutes

Is there an easier formula?

I would like to add the two calculated dates so I have one total.
I would also like it to calculate days, hours and minutes based on working days of Monday to Friday 9am to 5pm.
Reply With Quote
  #2  
Old 06-24-2013, 04:42 AM
Lonercom Lonercom is offline Formula to calculate Date and Time Windows XP Formula to calculate Date and Time Office 2007
Novice
 
Join Date: Jun 2013
Posts: 6
Lonercom is on a distinguished road
Default

I had to convert the date/time to US from euro to get this to work using the date/time in the corresponding cells.

In A2 I used =SUM(B1-A1)
In A3 I used =SUM(D1-C1)
In A4 I used =SUM(A2,A3)

A2-A4 I formatted cells custom HH:MM:SS

A1: 05:05:36
B1: 01:02:32
C1: 06:08:08

Sorry, didn't see that you wanted to exclude weekends. Found this in another thread that mau point you in the right direction:

=IF(COUNTIF(HOLIDAYS!$C$4:$C$14,A5),"h",IF(OR(F5=" Sat",F5="Sun")," ",SUMPRODUCT(--(WEEKDAY(A5+1-ROW(INDIRECT("1:"&DAY(A5))),2)<6),--ISNA(MATCH(A5+1-ROW(INDIRECT("1:"&DAY(A5))),HOLIDAYS!$C$4:HOLIDAYS !$C$14,0))))
Hope this helps, If it's acting odd, try switching to US Dates
Reply With Quote
  #3  
Old 06-25-2013, 04:25 PM
BobBridges's Avatar
BobBridges BobBridges is offline Formula to calculate Date and Time Windows 7 64bit Formula to calculate Date and Time Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 674
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

Funckyfido, if Lonercom's answer satisfies you then feel free to ignore this (and mark the thread "solved"). But if all you want is display, I think you can indeed calculate the difference between your start and end date; just do the subtraction:

=B1-A1

Then change the format of the result to "[h]:mm". The brackets around the 'h' cause it to display all the hours involved, not topping out at 24. And after that, of course, adding them up is just as simple as a SUM function.

As for skipping weekends, I'm not sure what Lonercom is reading but if no work takes place on the weekends then the start and end timestamps will reflect that; no need to do anything special. But maybe I misunderstood that part of the question.
Reply With Quote
Reply

Tags
date, formulae, time

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need a formula to calculate turnaround time Rockitman31 Excel 9 09-07-2015 09:41 AM
Formula to calculate Date and Time Formula to auto calculate Day of the week based on Date prasad@dmci.ca Excel 1 11-29-2011 01:05 PM
Formula to calculate Date and Time Calculate elapsed time in Word table? Snvlsfoal Word Tables 1 08-11-2011 05:42 AM
Formula to calculate Date and Time What formula should I use to calculate commission? grs Excel 3 02-21-2011 02:17 AM
Formula to calculate Date and Time How to get cells to calculate time? jrasche2003@yahoo.com Excel 2 02-09-2007 07:10 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 10:23 PM.


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