how in the world can I

confused · Apr 18th, 2002, 07:16 PM

do this? I'm new to this board as well as excel. My problem is I'm trying to figure out a forumla that will give me a total time (hours & mins) but also use the date. such as, I have a job that starts on 4/8 1943 and ends on 4/9 2030. the forumla I have now only shows 47 mins. but such be 24hr 47 mins.
can anyone help me? TIA

brettvba · Apr 18th, 2002, 07:27 PM

just a quick one if you change the dates around 8/4 and 9/4 then subtract the differece and format the cell to custom d-hhmm it will give you 1-47 any help?

brettvba · Apr 18th, 2002, 07:29 PM

start 8/04/2002 19:43
stop 9/04/2002 20:30
1- 00:47
assuming 8/4 is in c2 and 9/4 is in c3
=c3-c2
then go into format cells
custom and enter d-hh:mm

confused · Apr 18th, 2002, 08:03 PM

Brettvba, Thanks for the quick reply. I'll give you answer a shoot. However I was sorta thinking along the lines as the following setup.
B1= start Date
C1= start time
D1= end date
E1= end time

the current formula I'm using only figures out the total for C1 and E1
=(2400-C1)+(2400+E1)-(2400)

But I need to add the other two columns and get them into the formula. Hope you can help me?? TIA

brettvba · Apr 18th, 2002, 08:12 PM

ok how about assuming your cells are in where you wanted b2 c2 etc

=SUM(D2-B2)+SUM(E2-C2)
and format the cell to
d" Day/s "hh:mm

that help?

[ This Message was edited by: brettvba on 2002-04-18 19:14 ]

Yogi Anand · Apr 18th, 2002, 08:25 PM

Quote:

On 2002-04-18 18:16, confused wrote:
do this? I'm new to this board as well as excel. My problem is I'm trying to figure out a forumla that will give me a total time (hours & mins) but also use the date. such as, I have a job that starts on 4/8 1943 and ends on 4/9 2030. the forumla I have now only shows 47 mins. but such be 24hr 47 mins.
can anyone help me? TIA

Hi Friend:
No need to be confused. Let us say your start time 4/8 19:43 is in cell A2, and end time 4/9 20:30 is in cell B2, then in cell C2 put the formula:
=B2-A2 and format C2 as CUSTOM FORMAT [h]:mm:ss
and you wii see the result as 24:47:00

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!
[/quote]

_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 18:27 ]

confused · Apr 18th, 2002, 08:34 PM

I wish I could say that helped, but sorry it doesn't. Thanks for trying to help tho. What I really need is a formula that would add/subtact the two date columns together and add/subtact the two time columns and give me the total. my date columns are in dd-mm-yyyy format and my time is in hh:mm:ss format.

brettvba · Apr 18th, 2002, 08:42 PM

sorry have i misunderstood are you after days and hours/minutes or just the total minutes or just hours/minutes if its days and hours/minutes my example works well

Yogi Anand · Apr 18th, 2002, 08:52 PM

Quote:

On 2002-04-18 19:03, confused wrote:
Brettvba, Thanks for the quick reply. I'll give you answer a shoot. However I was sorta thinking along the lines as the following setup.
B1= start Date
C1= start time
D1= end date
E1= end time

the current formula I'm using only figures out the total for C1 and E1
=(2400-C1)+(2400+E1)-(2400)

But I need to add the other two columns and get them into the formula. Hope you can help me?? TIA

Use the formula

=D1-B1+E1-C1 ... CUSTOM format it as [h]:mm:ss to get the result 24:47:00

HTH

Yogi Anand

confused · Apr 18th, 2002, 09:12 PM

Yogi your the man .... Thanks a whole lot, I tried it out and was blown out of my chair ..lol. Again Thanks for the help..

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 18th, 2002, 07:16 PM	#1
confused New Member Join Date: Apr 2002 Posts: 5	do this? I'm new to this board as well as excel. My problem is I'm trying to figure out a forumla that will give me a total time (hours & mins) but also use the date. such as, I have a job that starts on 4/8 1943 and ends on 4/9 2030. the forumla I have now only shows 47 mins. but such be 24hr 47 mins. can anyone help me? TIA

Apr 18th, 2002, 07:27 PM	#2
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	just a quick one if you change the dates around 8/4 and 9/4 then subtract the differece and format the cell to custom d-hhmm it will give you 1-47 any help?

Apr 18th, 2002, 07:29 PM	#3
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	start 8/04/2002 19:43 stop 9/04/2002 20:30 1- 00:47 assuming 8/4 is in c2 and 9/4 is in c3 =c3-c2 then go into format cells custom and enter d-hh:mm

Apr 18th, 2002, 08:03 PM	#4
confused New Member Join Date: Apr 2002 Posts: 5	Brettvba, Thanks for the quick reply. I'll give you answer a shoot. However I was sorta thinking along the lines as the following setup. B1= start Date C1= start time D1= end date E1= end time the current formula I'm using only figures out the total for C1 and E1 =(2400-C1)+(2400+E1)-(2400) But I need to add the other two columns and get them into the formula. Hope you can help me?? TIA

Apr 18th, 2002, 08:12 PM	#5
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	ok how about assuming your cells are in where you wanted b2 c2 etc =SUM(D2-B2)+SUM(E2-C2) and format the cell to d" Day/s "hh:mm that help? [ This Message was edited by: brettvba on 2002-04-18 19:14 ]

Apr 18th, 2002, 08:34 PM	#7
confused New Member Join Date: Apr 2002 Posts: 5	I wish I could say that helped, but sorry it doesn't. Thanks for trying to help tho. What I really need is a formula that would add/subtact the two date columns together and add/subtact the two time columns and give me the total. my date columns are in dd-mm-yyyy format and my time is in hh:mm:ss format.

Apr 18th, 2002, 08:42 PM	#8
brettvba MrExcel MVP Join Date: Feb 2002 Location: Christchurch New Zealand Posts: 1,030	sorry have i misunderstood are you after days and hours/minutes or just the total minutes or just hours/minutes if its days and hours/minutes my example works well

Apr 18th, 2002, 09:12 PM	#10
confused New Member Join Date: Apr 2002 Posts: 5	Yogi your the man .... Thanks a whole lot, I tried it out and was blown out of my chair ..lol. Again Thanks for the help..