Calculate Same Date

[alamgiritbd]

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]

what about

C2: =B1+1-A1

Format C1 as ,

 

[alamgiritbd]

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?

 

[Hui]

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

 

[alamgiritbd]

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]

=B1-A1

 

[alamgiritbd]

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)))

 

[alamgiritbd]

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)))

 

[keymaster]

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

 

[alamgiritbd]

Yes, I am looking for this.

Thanks A lot both of you...........