Help Creating an Excel IF Statement

April 16, 2009 at 23:20:32
Specs: Windows XP
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?

See More: Help Creating an Excel IF Statement

Report •


#1
April 17, 2009 at 08:12:39
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?


Report •

#2
April 19, 2009 at 22:29:29
i am counting the whole day..

the formula u gave does not work.. could u plz suggest


Report •

#3
April 20, 2009 at 07:09:55
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 AM

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

3) Begin date is 3/4/09 10:15 AM
end date is 4/4/09 7:25 PM

Thanks


Report •

Related Solutions

#4
April 22, 2009 at 02:52:28
1) Begin date is 3/4/09 10:15 AM
end date is 4/4/09 9:30 AM the answer is 1

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

3) Begin date is 3/4/09 10:15 AM
end date is 4/4/09 7:25 PM the answer is 2


Report •

#5
June 2, 2009 at 23:05:16
plz some one can help?

Report •

#6
June 3, 2009 at 08:21:18
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 PM leads me to believe your trying to account for some type of overtime, but not sure what your doing.

MIKE

http://www.skeptic.com/


Report •


Ask Question