Solved: subtract date/time formatted fields

[pdeshazier]

i have a spreadsheet which contains a begin and end time, both formatted as custom m/d/yy h:mm AM/PM;@. my formula to calculate hours and minutes between the two times is =TEXT(B1-A1, "h:mm"). however, it's not working. my begin time (A1) is 6/6/08 10:59 AMand my end time (B1) is 6/8/08 11:04 AMbut the result of my formula is 0:04. can someone help? thanks.

[Bob Phillips]

Just use

=B1-A1

and format as [h]:mm

[pdeshazier]

does it matter how my begin and end times are formatted?

[RonMcK]

No, because Excel saves and stores the cell value as a decimal number combining the julian number of the day and the decimal for the time within the day. Formating is for display purposes only.

HTH,

[pdeshazier]

great, thanks. will i learn these kinds of things if i subscribe to the site's excel vba training?

[Bob Phillips]

No because that is not VBA, it is Excel.

[pdeshazier]

this is what my results look like now, which are correct:
48:04:5122:59:515:02:5199:53:51
however, i'm trying to use a formula IF(RESULT>24,"PROB","") to flag accounts which were not corrected in our required 24 hour time frame. The result i get for the first one (which is 48) is "" when it should be "PROB"

[pdeshazier]

okay, thanks

[RonMcK]

this is what my results look like now, which are correct:
48:04:51 22:59:51 5:02:51 99:53:51
however, i'm trying to use a formula IF(RESULT>24,"PROB","") to flag accounts which were not corrected in our required 24 hour time frame. The result i get for the first one (which is 48) is "" when it should be "PROB"

Pam,

Do you have these dates formated as [h]:mm:ss ? When I put these in my Excel 2004 (Mac), it converted the 1st and 4th entries to hours less than 24 putting the overage into days in the date portion.

In addition, before you can compare to 24 hours, you must mulitply by 24 to get hours as a whole number. Using your first entry we have:

Your Excel entry: 48:04:51
Date/time stored: 2.0033680556 (this * 24 = following line) (remember: days.time)
Convert to hoiurs: 48.08083333


HTH,

[pdeshazier]

sorry, but i'm an idiot and am apparently not understanding. do i take that result field and * by 24? if i do i get 1153. do i then format that as a number? if i do, i get 48. am i doing it right?

[pdeshazier]

i think i got it working!!!! thanks to all who helped! i LOVE this site

[RonMcK]

Pam,

Yes, you convert from time format to numeric format, then, do the math.

Sorry for the confusion.

One last request. Go into your thread tools (I think it is) and mark the thread Solved.