Thanks:  0
Likes:  0

# Thread: how in the world can I

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

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

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

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

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

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

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

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

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•