Subtracting a duration from a time to calculate a time.

S

SRadmin

I need a formula that calculates the like this:

Time - Duration = Time

Formated to look like this

8:10 AM 22:17 9:53 AM

where the duration is [hh]:mm (I think)

so I need to know how to format the cells and the formula.
 
B

Bernie Deitrick

If they are just times:

=IF(B2>A2,A2+1-B2,A2-B2)

If they are date and times formatted for time, then use

=A2-B2

In either case, format for time AM/PM.


HTH,
Bernie
MS Excel MVP
 
S

SRadmin

Worked Perfectly! Thanks soo much.

Bernie Deitrick said:
If they are just times:

=IF(B2>A2,A2+1-B2,A2-B2)

If they are date and times formatted for time, then use

=A2-B2

In either case, format for time AM/PM.


HTH,
Bernie
MS Excel MVP


SRadmin said:
I need a formula that calculates the like this:

Time - Duration = Time

Formated to look like this

8:10 AM 22:17 9:53 AM

where the duration is [hh]:mm (I think)

so I need to know how to format the cells and the formula.
 
S

SRadmin

So now I would like to see if I can take this formula one step further.
I now have it set where the formula is just like you said, but I changed the
format.

Time - Duration = Time

Format is now

10/2/08 7:51 AM 14:01 10/1/08 5:50 PM

Now I would like to add days to the Duration.

like this? 02:14:01 where the format is: dd:[hh]:mm

is that posible? or would I need to calculate (# of days * 24) + [hh]

I don't know if I am being clear.

Bernie Deitrick said:
If they are just times:

=IF(B2>A2,A2+1-B2,A2-B2)

If they are date and times formatted for time, then use

=A2-B2

In either case, format for time AM/PM.


HTH,
Bernie
MS Excel MVP


SRadmin said:
I need a formula that calculates the like this:

Time - Duration = Time

Formated to look like this

8:10 AM 22:17 9:53 AM

where the duration is [hh]:mm (I think)

so I need to know how to format the cells and the formula.
 
B

Bernie Deitrick

You're very clear.

One option would be to use a separate column for the integer number of days:

Time Days Hours New Time


and use the formula

=A2-B2-C2

in cell D2.

Another would be to use the entry

Days + hrs/24 +minutes/1440

in your current time duration cell.


HTH,
Bernie
MS Excel MVP


SRadmin said:
So now I would like to see if I can take this formula one step further.
I now have it set where the formula is just like you said, but I changed the
format.

Time - Duration = Time

Format is now

10/2/08 7:51 AM 14:01 10/1/08 5:50 PM

Now I would like to add days to the Duration.

like this? 02:14:01 where the format is: dd:[hh]:mm

is that posible? or would I need to calculate (# of days * 24) + [hh]

I don't know if I am being clear.

Bernie Deitrick said:
If they are just times:

=IF(B2>A2,A2+1-B2,A2-B2)

If they are date and times formatted for time, then use

=A2-B2

In either case, format for time AM/PM.


HTH,
Bernie
MS Excel MVP


SRadmin said:
I need a formula that calculates the like this:

Time - Duration = Time

Formated to look like this

8:10 AM 22:17 9:53 AM

where the duration is [hh]:mm (I think)

so I need to know how to format the cells and the formula.
 
S

SRadmin

I don't know why I couldn't figure that out on my own. Worked perfectly yet
again!
Thanks a bunch.

Bernie Deitrick said:
You're very clear.

One option would be to use a separate column for the integer number of days:

Time Days Hours New Time


and use the formula

=A2-B2-C2

in cell D2.

Another would be to use the entry

Days + hrs/24 +minutes/1440

in your current time duration cell.


HTH,
Bernie
MS Excel MVP


SRadmin said:
So now I would like to see if I can take this formula one step further.
I now have it set where the formula is just like you said, but I changed the
format.

Time - Duration = Time

Format is now

10/2/08 7:51 AM 14:01 10/1/08 5:50 PM

Now I would like to add days to the Duration.

like this? 02:14:01 where the format is: dd:[hh]:mm

is that posible? or would I need to calculate (# of days * 24) + [hh]

I don't know if I am being clear.

Bernie Deitrick said:
If they are just times:

=IF(B2>A2,A2+1-B2,A2-B2)

If they are date and times formatted for time, then use

=A2-B2

In either case, format for time AM/PM.


HTH,
Bernie
MS Excel MVP


I need a formula that calculates the like this:

Time - Duration = Time

Formated to look like this

8:10 AM 22:17 9:53 AM

where the duration is [hh]:mm (I think)

so I need to know how to format the cells and the formula.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top