From: blueriver on 19 Feb 2010 12:53 I am sure this question has been answered before but I am dizzy trying to sort through all of the other posts when I run a search. I have a spreadsheet that does some pretty basic time in and time outs. I have a validation table only allowing time on the quarter of an hour for the time in and time out. It is formatted in 12:00 AM time. In this particular instance I cannot get my formula to work when the end time is 12:00 AM. E.g.: Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads =IF(E9>0,(E9D9)*1440/60,""). This formula returns nothing when the end time is 12:00 AM. I need it to return 2.00 for the amount of hours. Any thoughts and Thanks! CRM
From: Eduardo on 19 Feb 2010 13:11 Hi, look into CPearson web http://www.cpearson.com/excel/overtime.htm there you will find times examples and the formulas for each calculation Hope this helps "blueriver" wrote: > I am sure this question has been answered before but I am dizzy trying to > sort through all of the other posts when I run a search. > > I have a spreadsheet that does some pretty basic time in and time outs. I > have a validation table only allowing time on the quarter of an hour for the > time in and time out. It is formatted in 12:00 AM time. > > In this particular instance I cannot get my formula to work when the end > time is 12:00 AM. E.g.: > > Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads > =IF(E9>0,(E9D9)*1440/60,""). This formula returns nothing when the end time > is 12:00 AM. I need it to return 2.00 for the amount of hours. > > Any thoughts and Thanks! > CRM >
From: Daryl S on 19 Feb 2010 13:16 CRM  12:00 AM is midnight, which is 0 time. Depending on how you get your time, you might just need to test for E9>=0, like this: =IF(E9>=0,(E9D9)*1440/60,"") Check for negative hours (depends on how you have the time). If you get them, then you will need to add 24 hours to the negative amount.  Daryl S "blueriver" wrote: > I am sure this question has been answered before but I am dizzy trying to > sort through all of the other posts when I run a search. > > I have a spreadsheet that does some pretty basic time in and time outs. I > have a validation table only allowing time on the quarter of an hour for the > time in and time out. It is formatted in 12:00 AM time. > > In this particular instance I cannot get my formula to work when the end > time is 12:00 AM. E.g.: > > Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads > =IF(E9>0,(E9D9)*1440/60,""). This formula returns nothing when the end time > is 12:00 AM. I need it to return 2.00 for the amount of hours. > > Any thoughts and Thanks! > CRM >
From: Teethless mama on 19 Feb 2010 13:16 =MOD(E9D9,1)*24 Format cell as General "blueriver" wrote: > I am sure this question has been answered before but I am dizzy trying to > sort through all of the other posts when I run a search. > > I have a spreadsheet that does some pretty basic time in and time outs. I > have a validation table only allowing time on the quarter of an hour for the > time in and time out. It is formatted in 12:00 AM time. > > In this particular instance I cannot get my formula to work when the end > time is 12:00 AM. E.g.: > > Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads > =IF(E9>0,(E9D9)*1440/60,""). This formula returns nothing when the end time > is 12:00 AM. I need it to return 2.00 for the amount of hours. > > Any thoughts and Thanks! > CRM >
From: John on 19 Feb 2010 13:20 Hi Blueriver The problem is that XL does not know that 12 AM is the next day if you put the date & time in the cell, your formula should work . "Formating as General " e.g. Time IN 2/19/2010 22:00 Time OUT 2/20/2010 00:00 HTH John "blueriver" <blueriver(a)discussions.microsoft.com> wrote in message news:C384E142799C4399BEF4B7171F47F278(a)microsoft.com... >I am sure this question has been answered before but I am dizzy trying to > sort through all of the other posts when I run a search. > > I have a spreadsheet that does some pretty basic time in and time outs. I > have a validation table only allowing time on the quarter of an hour for the > time in and time out. It is formatted in 12:00 AM time. > > In this particular instance I cannot get my formula to work when the end > time is 12:00 AM. E.g.: > > Start time (D9) is 10:00 PM, End time (E9) is 12:00 AM. My formula reads > =IF(E9>0,(E9D9)*1440/60,""). This formula returns nothing when the end time > is 12:00 AM. I need it to return 2.00 for the amount of hours. > > Any thoughts and Thanks! > CRM >

Next

Last
Pages: 1 2 Prev: Merging cells Next: SOLVED: Input calculation in specified rows and columns 