I have dates in cell (C2) and (D2) format is 3/4/09 10:15 AM . I would like to subtract D2 - C2 and the value should come in E2 in number format..but if the time in D2 is more than 6:00 PM then the value of E2 should be incremented by 1

Can anyone help?

you can use for the "IF" part of your statement =IF(TIMEVALUE(text(D2,"hh:mm:ss"))>=3/4,value_if_true,value_if_false)Are you counting whole days or part days? If whole days, are you rounding or using CEILING?

i am counting the whole day.. the formula u gave does not work.. could u plz suggest

when you say "the formula does not work", what error are you getting? Can you provide some answers to the following scenarios? Just say what you would want the answer to be in numeric format (please note I am quoting dates in d/m/yy format).

1) Begin date is 3/4/09 10:15 AM

end date is 4/4/09 9:30 AM2) Begin date is 3/4/09 10:15 AM

end date is 3/4/09 1:00 PM3) Begin date is 3/4/09 10:15 AM

end date is 4/4/09 7:25 PMThanks

1) Begin date is 3/4/09 10:15 AM

end date is 4/4/09 9:30 AM the answer is 12) Begin date is 3/4/09 10:15 AM

end date is 3/4/09 1:00 PM the answer is 03) Begin date is 3/4/09 10:15 AM

end date is 4/4/09 7:25 PM the answer is 2

plz some one can help?

A bit of clarification:

the value should come in E2 in number format

What kind of number format?Just changing the cell to a number format will give you the number of days/hours between the two dates with the days as whole numbers and the hours as a decimal fraction so

4/4/2009 9:30 minus 3/4/2009 10:15 = 30.97

What is it your trying to do?

Is it the number of days that is significant?

Is it the number of hours that is significant?Your statment:

but if the time in D2 is more than 6:00 PMleads me to believe your trying to account for some type of overtime, but not sure what your doing.MIKE

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History