how in the world can I

[confused]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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..