=(IF(OR(H8="-", I8="-", H8="", I8=""), 0, IF(OR(J8="h1",J8="H2"), DATEDIF(H8, I8, "d") - 0.5, DATEDIF(H8, I8, "d"))))
The above formula should be entered in K8 and it means the following: IF cells H8 or I8 contain no/blank value or a dash (-) THEN 0 Else IF cell J8 contains "H1" or "H2" THEN subtract 0.5 from the date difference between dates in cells H8 and I8 ELSE date difference between dates in cells H8 and I8 Hope it helps! Best Regards, Zujar… An optimist laughs to forget and a pessimist forgets to laugh! -----Original Message----- From: AccessIndia [mailto:[email protected]] On Behalf Of Mukesh Sent: Monday, April 08, 2013 6:52 AM To: [email protected] Subject: Re: [AI] Formula correction Dear Zujar, Thank you for your response. I know there is no function 'datedif' in excel but it works in excel. I have inserted it in my database easily and achieve the appropriate output. Yesterday I have completed my leave register successfully with the help of all of you time to time. h1: leave for first session, morning h2: second half, afternoon Thank you for time to time guidance to solve my problems. -----Original Message----- From: Zujar Shabbir Kanchwala Sent: Sunday, April 07, 2013 10:06 PM To: [email protected] Subject: Re: [AI] Formula correction Mukeshji, 1. What do you mean by H1 and H2 - these are cell references or string values? 2. Does the cell K8 contain date difference? 3. There's no function in Excel called DATEDIF, you can get the difference in two dates by subtracting them. It would be better understood if you can give an example of what you want to achieve. Best Regards, Zujar… An optimist laughs to forget and a pessimist forgets to laugh! -----Original Message----- From: AccessIndia [mailto:[email protected]] On Behalf Of Mukesh Sent: Thursday, April 04, 2013 7:29 AM To: Access India Subject: [AI] Formula correction Dear Friends, I have created a formula for my database. It is giving partial result. I want to modify it for better output. My condition is as follows: If cell j8= h1 then the formula should minus 0.5 from date difference in cell k8. If cell j8= h2 then the formula should minus 0.5 from date difference in cell k8. If cell j8= - then the formula should remain unchanged. My formula is as follows: =(IF(H8="-",0,IF(I8="-",0,IF(H8="",0,IF(I8="",0,IF(J8="h1",DATEDIF(H8,I8,"d")+1)-0.5))))) It’s very urgent to complete my database so please modify it for me today. Waiting for corrected formula. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Skype id: mukeshbaviskar Blog: http://mukeshbaviskar.blogspot.com Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/[email protected]/ To unsubscribe send a message to [email protected] with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/[email protected]/ To unsubscribe send a message to [email protected] with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in Regards Mukesh Baviskar Mobile: 9403161157 Skype id: mukeshbaviskar Blog: http://mukeshbaviskar.blogspot.com Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/[email protected]/ To unsubscribe send a message to [email protected] with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/[email protected]/ To unsubscribe send a message to [email protected] with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
