• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to subtract time in a specific format

O

obsrvr

New Member
Hi I have a problem with figuring an appropriate formula to subtract time. Suppose I have a time in mm:ss format showing 05:01 (meaning 5 minutes and 1 second) in A1. In A2, the column "Slow", need to have a formula, if the time in A1 is less than [specified time] say, 2 min, then return "No", otherwise "Yes". For some reason the formula I try to write for Excel would not work, please help out. this is my formula A1 contains "12:05:31 AM" in mm:ss format displayed as 05:31. This is my formula: =IF(A1<1:50, "No","Yes")


Now, I will be pasting from another spreadsheet and hope this won;t be a problem.


Thanks in advance.
 
Hi Observer..


Try..

Code: 
=IF(MOD(A1,1)<TIMEVALUE("0:1:50"),"No","Yes")


Regards,

Deb
 
Hi Obs..


* Observe closely.. what is looks like "12:05:31 AM" is actually "01-JAN-1900 12:05:31 AM"

convert the cell to "DD-MMM-YYYY HH:MM:SS" format..

* Again observe closely.. what is looks like "01-JAN-1900 12:05:31 AM" is actually "0.209027777777778"

convert the cell to "General Format" format..


So, EXCEL always treat TIME as DATE & TIME.. and again treat all DATE & TIME as NUMBER..

MOD function.. actually gives us REMINDER after a number is DIVIDED by a DIVISOR.. So in above case.. it gives us TIME part after a date & time, divided by 1..


Regards,

Deb
 
If you really want Yes/No

=IF(A1<TIME(0,1,50),"No","Yes")


If you are Happy with True/False

=A1>TIME(0,1,50)
 
Thanks a lot for your help Hui and Deb!


@deb: I have to say that this is rather subtle, I am confused. What is our divisor? I mean is not anything divided by 1 gives the same number? Sorry if I missing something very basic.


Obs
 
Obs


Time is simply numbers between 0 midnight and 1 the next Midnight

So 0.5 is 12 Noon and 0.25 is 6am 0.75 is 6pm


So you can add 1 minute to 12 Noon as =0.5+1/(60*24)

where 60 * 24 = 1440 is the number of minutes in a day

1 / 1440 is the fraction of a day 1 minutes equals

same for seconds


1 min 30 seconds = 90 seconds

= 90/(24*60*60) 'th of a day


The Time command simply converts the fractions for you

so

=Time(1, 5, 30)

= 1 Hr 5 mins 30 seconds

= 1/24 + 5/(24x60) + 30/(24x60x60)

= 0.04548611


Hence to add/subtract times simply convert the measures to a fraction of a Day and add/subtract away


How the dates/times are displayed has no bearing on the above.

Excel uses the Default or a Custom Number format to display dates/Times and will automatically increment days, Hrs etc if you come up with an answer like 1.25 days

it means 6am the next day
 
Hi Obs ,


The MOD function gives the remainder when one number is divided by another.


For example if we have the two numbers 7 and 3 , then =MOD(7,3) will give the remainder when 7 is divided by 3 , which is 1 ; thus =MOD(7,3) will display 1.


When the divisor is 1 , then the remainder is the fractional or decimal part. For example , if a number such as 7.6 is divided by 1 , the remainder is 0.6 ; hence =MOD(7.6,1) will display 0.6


The =MOD(number,1) is used to return the decimal part of number ; the other associated function is INT , which returns the integer part of a number when divided by another ; thus =INT(7/3) will return 2. When the divisor is 1 , the INT function reduces to =INT(number) ; thus =INT(7.6) will return 7.


Of course , there is much more to this ; read about the other functions in this category TRUNC , FLOOR , CEILING.


Narayan
 
I can't thank all of you enough here, it is very clear now!


@ Deb: Could not agree more, this is amazing.


Totally off topic, has anyone taken the financial modelling class offered here or saw some reviews by those who did? Thanks for help.
 
You must log in or register to reply here.
Back
Top