Excel Date Question - Greater than.

[RobertLDaniels]

Hello, I have a date and time that is pullled within a report (6/27/2011 21:22). I need help on the excel formula that will reference that date in column A and if the hours and minutes are greater than 3pm (15:00) then carry the date to the next workday and start the time at 8pm (6/27/2011 8:00), that new date and time would then be shown in column B. If there a way to do this?

 

[Jared_8878]

3pm (15:00) IS .63

so to determine if the date is greater than that you could:

if(((date in question) - int(date in question))>.63, do this, do that)

=IF((A1-INT(A1))>0.63,INT(A1)+0.834,"something else")

date in A1, formula in B1

 

[RobertLDaniels]

That worked, thanks. How did you come up with the .63 for 3pm. Is there a calculation to figure out say 1:30pm?

 

[Taul]

1/24th * the hour

(1/24)*15 = 0.625

alternatively, enter the time in the cell as 15:00 then format the cell as General and the cell should show 0.625 (or 0.63 if you format for two places)

 

[RobertLDaniels]

Thank you all for the help with this.