Microsoft Office Forums Formula to calculate Date and Time
 Register FAQ Search Today's Posts Mark Forums Read

#1
06-19-2013, 06:45 AM
 funkyfido Windows XP Office 2007 Novice Join Date: Jun 2013 Posts: 24
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.
#2
06-24-2013, 04:42 AM
 Lonercom Windows XP Office 2007 Novice Join Date: Jun 2013 Posts: 6

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
#3
06-25-2013, 04:25 PM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 674

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.

 Tags date, formulae, time

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Rockitman31 Excel 9 09-07-2015 09:41 AM prasad@dmci.ca Excel 1 11-29-2011 01:05 PM Snvlsfoal Word Tables 1 08-11-2011 05:42 AM grs Excel 3 02-21-2011 02:17 AM 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.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top