View Single Post
  #14   Report Post  
Old March 30th 16, 11:58 AM posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,866
Default If statement to compare time cell to a time

Hi,

Am Wed, 30 Mar 2016 03:32:21 -0700 (PDT) schrieb
:

I want to calculate the In and out time of employees. At times folks come at 9am (30th March) and leave at 2am(31st march). Hence the total time spent is 17 hours. Unable to apply this formula. Also once i know that the the number of hours spent in the office is 6 hours then autmatically in the other sheet/subsheet it populates "P" or "Present". If less than 6 hours then "H" or "Half Day"


what is the cell content?
If you have in A1 and B1 date and hour then
=B1-A1
If you only have times then in C1:
=MOD(B1-A1,1)

Then in the other sheet
=IF(Sheet1!C1TIME(6,,),"Present","Half Time")
or
=IF(Sheet1!C10.25,"Present","Half Time")


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional