• 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.

Calculate Same Date

A

alamgiritbd

New Member
I have 3 columns on one sheet. First one for starting date (A1), second one for ending date (B1) and last one for calculate the date/total (C1). It’s calculated date as normally and also need to calculate when date is same. And an option for calculate same date as half day.


For ex: dd-mm-yy


A1=12-12-2010 ------ B1=12-12-2010-------C1=1 day

And I need an option when I click on that or put some value then that cell (C1) calculate as 0.5 day.

And also need when A1=12-12-2010 ------ B1=13-12-2010-------C1=2 days


Is it possible?
 
Hui,


Thanks for quick reply.

It’s working. Thanks :)


But what about 0.5 day? Can you help me on that?

How i calculate that is same cell?
 
What are your rules?


You will need to enter dates in the format (or what ever other local format you may be using)

12/12/2010 6:00:00 (6am)

12/12/2010 12:00:00 (12 Noon)

12/12/2010 18:00:00 (6pm) etc
 
Hui,


thanks again. i found it's working for 1.5 day but i need 0.5 day.Would you pls see this link:

http://rapidshare.com/files/439321803/test.xlsx
 
Hui,


Thanks for your support. And sorry for my miss communications.

Finally I made this formula with your help.

1st one is work perfectly but when I use 2nd formula then it’s show #NAME?


(1st)R16: =IF(O16=0.5,"0.5",IF(O16=1,(L16+1-G16)))

(2nd)R16: =IF(O16=Half,"0.5",IF(O16=Full,(L16+1-G16)))
 
Hui,


Thanks for your support. And sorry for my miss communications.


Finally I made this formula with your help.

It is working but when L16 & G16 is blank then R16 show 1. I need to remove 1 when L&G16 is blank. I need there 0/blank cell.


R16: =IF(O16=0.5,"0.5",IF(O16=1,(L16+1-G16)))
 
You can modify =IF(O16=0.5,"0.5",IF(O16=1,(L16+1-G16))) this to,

=IF(O16=0.5,"0.5",IF(AND(O16=1,L16<>"",G16<>""),(L16+1-G16),"")) to show blanks when L16 & g16 are blanks
 
You must log in or register to reply here.
Back
Top