Calculate time which is past midnight

[stoey]

Hi,

This is very dumb of me but can someone tell me how to calculate time past midnight?

Like in A1 I have 11:35 PM and in B1 I have 12:06 AM, time difference should atleast be 1 minute or 00:01 in C1.

Regards,

Stoey

[zbor]

try =MOD(B1-A1,1)

The difference shoudl be 31 min

[stoey]

hi zbor,

thanks for the reply. I also apologize for not being clear...what I would like to accomplish is that with the time difference of 00:31 minutes the display on C1 should be 00:01 which is the over limit of the given 30 minutes allowance...

regards,

stoey

[zbor]

Maybe this:

=B1-MAX(A1-"23:30",0)

[arthurbr]

Perhaps

Please Login or Register  to view this content.

[stoey]

Hello zbor and arthur,

I appreciate your quick response on this. I tried both of your approach. Zbor's suggestion is limited to the 23:30 time while on arthur's approach, it did work and gave me the result which is 00:01 however when the time difference for two values like 10:35 PM - 11:05 PM instead of giving me the results of 00:00 since the time difference is exactly 30 minutes, it displays ######## instead...but when the time difference is 31 minutes it gives me the correct excess value which is 00:01 or 1 minute. Sorry for being a pain here...

Regards,

Stoey

[arthurbr]

In your example it should be 0.
Are you using exact times or rounded times?
Could the difference be smaller than 30 min?

[stoey]

hi arthur,

Your question:

In your example it should be 0.
Are you using exact times or rounded times?

I am using exact time

Could the difference be smaller than 30 min?

There is a certain limit of 30 minutes only...So if the time difference is like 00:31 minutes then it should display the excess of 1 minute from the 30 minute limit as 00:01...if the time difference is exactly 00:30 minutes then it should display as 00:00 since there is no excess of the 30 minute time limit. if the time difference is less than 00:30 minutes like 00:25 then it should still display as 00:00 or ( the word "on time" instead of 00:00 value)

Regards,

stoey

[zbor]

Mybe this:

=MAX(0, MOD(B1-C1,1)-"00:30")

[arthurbr]

Please Login or Register  to view this content.

works for me

[stoey]

Hi again zbor and arthur,

tried both approach..
zbor's approach keeps giving me the value 00:00 regardless if the time difference is in excess of the 30 minutes limit or not. on the other hand, arthur's approach is working however a few question here I have...this formula is good with the 30 minutes limit...how would the formula be if the limit cap is 15 minutes? has the limit cap has something to do with 1/48 on your formula arhtur? can you please provide me info so that incase i would like to change the limit cap every now and then I can set the appropriate formula...

regards,

stoey

[HenryCrun]

has the limit cap has something to do with 1/48 on your formula arhtur? can you please provide me info so that incase i would like to change the limit cap every now and then I can set the appropriate formula...

30 mins = 1/48 of a day

B1 - A1 gives the difference between the two times represented as a decimal number (the decimal part being the proportion of the day)
MOD(B1 - A1,1) discounts any full days between the two times
IF(MOD(B1 - A1,1) <= 1/48... compares the decimal value of the difference to the decimal value of 30 mins

[arthurbr]

For XL one day =1, one hour =1/24, 1/2hr = 1/48,1/4 hr =1/96...

[stoey]

wow! thank you very much for all the info...I do understand now what does 1/48 mean...but how would it me if the limit cap would be like 32 minutes? how would this be in fraction?

[arthurbr]

Try 32/1440

[HenryCrun]

wow! thank you very much for all the info...I do understand now what does 1/48 mean...but how would it me if the limit cap would be like 32 minutes? how would this be in fraction?

1 hour is 1/24 day
1 minute is 1/1440 day
1 second is 1/86400 day

Use the appropriate denominator, and the quantity of units as the numerator.

[stoey]

thank you so much arthur, henry and zbor for all your assistance! you guys rock!