Subtracting Date/Time

S

Sapphyre

I know this has been asked before, but I need a little bit more help.

Ok I have this situation:

A B C
D
Start Date/Time End Date/Time Total Time Total
Minutes
08/15/07 14:56 08/16/07 16:02 1 days 1:06

I use format custom mm/dd/yy hh:mm for column A and B and just enter the
information. Column C I have formated custom hh:mm and use this formula
=(DAY(B2)-DAY(A2))&" days "&TEXT(B2-A2,"hh:mm")
It doesn't have to look like 1 days 1:06, but that's the only way I could
get the date/time to subtract right when it's over 24 hours. But the problem
with this formula is I can't figure out how to get column D to work. Is
there a formula that I could use in Column D where the answer would be 1506
(total minutes) in my example above?

Hope I explained that right.

Thanks, Sapphyre
 
S

Sapphyre

Thanks a bunch Toppers, that worked like a charm. I think I try to make
everything difficult, and you had a very easy solution...Thank you again.

Toppers said:
=B1-A1

format as [mm]



Sapphyre said:
I know this has been asked before, but I need a little bit more help.

Ok I have this situation:

A B C
D
Start Date/Time End Date/Time Total Time Total
Minutes
08/15/07 14:56 08/16/07 16:02 1 days 1:06

I use format custom mm/dd/yy hh:mm for column A and B and just enter the
information. Column C I have formated custom hh:mm and use this formula
=(DAY(B2)-DAY(A2))&" days "&TEXT(B2-A2,"hh:mm")
It doesn't have to look like 1 days 1:06, but that's the only way I could
get the date/time to subtract right when it's over 24 hours. But the problem
with this formula is I can't figure out how to get column D to work. Is
there a formula that I could use in Column D where the answer would be 1506
(total minutes) in my example above?

Hope I explained that right.

Thanks, Sapphyre
 
S

Sapphyre

Ok, I spoke too soon. While your [mm] did work for column D, I'm now having
a problem with my column C.

Here is the problem I'm having using this formula in column C
=(DAY(B2)-DAY(A2))&" days "&TEXT(B2-A2,"hh:mm")

If say I have this input below in my columns

A B C
D
Start Date/Time End Date/Time Total Time Total Minutes
08/15/07 22:23 08/16/07 00:20 1 days 1:57

There should not be 1 day 1:57 difference between A&B, it should only be
1:57 difference. As you can tell I'm very new at this, so not sure how to
get this to work.

Any help would be greatly appreciated.






Sapphyre said:
Thanks a bunch Toppers, that worked like a charm. I think I try to make
everything difficult, and you had a very easy solution...Thank you again.

Toppers said:
=B1-A1

format as [mm]



Sapphyre said:
I know this has been asked before, but I need a little bit more help.

Ok I have this situation:

A B C
D
Start Date/Time End Date/Time Total Time Total
Minutes
08/15/07 14:56 08/16/07 16:02 1 days 1:06

I use format custom mm/dd/yy hh:mm for column A and B and just enter the
information. Column C I have formated custom hh:mm and use this formula
=(DAY(B2)-DAY(A2))&" days "&TEXT(B2-A2,"hh:mm")
It doesn't have to look like 1 days 1:06, but that's the only way I could
get the date/time to subtract right when it's over 24 hours. But the problem
with this formula is I can't figure out how to get column D to work. Is
there a formula that I could use in Column D where the answer would be 1506
(total minutes) in my example above?

Hope I explained that right.

Thanks, Sapphyre
 
S

Sapphyre

Never mind, I found a different way to do Column C, instead of my lengthy
formula, I just used B-A and format d "days" hh:mm then in D column I used
B-A and format [mm] (like you had posted) and it all works.

Thanks again

Sapphyre said:
Ok, I spoke too soon. While your [mm] did work for column D, I'm now having
a problem with my column C.

Here is the problem I'm having using this formula in column C
=(DAY(B2)-DAY(A2))&" days "&TEXT(B2-A2,"hh:mm")

If say I have this input below in my columns

A B C
D
Start Date/Time End Date/Time Total Time Total Minutes
08/15/07 22:23 08/16/07 00:20 1 days 1:57

There should not be 1 day 1:57 difference between A&B, it should only be
1:57 difference. As you can tell I'm very new at this, so not sure how to
get this to work.

Any help would be greatly appreciated.






Sapphyre said:
Thanks a bunch Toppers, that worked like a charm. I think I try to make
everything difficult, and you had a very easy solution...Thank you again.

Toppers said:
=B1-A1

format as [mm]



:

I know this has been asked before, but I need a little bit more help.

Ok I have this situation:

A B C
D
Start Date/Time End Date/Time Total Time Total
Minutes
08/15/07 14:56 08/16/07 16:02 1 days 1:06

I use format custom mm/dd/yy hh:mm for column A and B and just enter the
information. Column C I have formated custom hh:mm and use this formula
=(DAY(B2)-DAY(A2))&" days "&TEXT(B2-A2,"hh:mm")
It doesn't have to look like 1 days 1:06, but that's the only way I could
get the date/time to subtract right when it's over 24 hours. But the problem
with this formula is I can't figure out how to get column D to work. Is
there a formula that I could use in Column D where the answer would be 1506
(total minutes) in my example above?

Hope I explained that right.

Thanks, Sapphyre
 

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