Formula dependant on date and time

[Dave2804]

Hi,

Not the greatest with excel, so i'll attempt to explain my query using pictures.

Bit of background, the 'Required Completion' and 'Actual Completion' dates are exported from an Asset management system for different priority highway maintenance jobs.

Below you can see that if the date in 'Actual Completion' is greater (later) than 'Required Completion' then a 'No' is outputted in the 'Job Completed on Time' column and if smaller (earlier) then a 'Yes'. Very simple and no issues so far. However, it has come to light that for a particular priority job the 'Actual Completion' is allowed to occur by 3 pm the following day of the 'Required Completion' to remain within SLA. Thus, what I would like is to know how I can amend the below formula to incorporate this. E.g something like IF(G301>(F301+""1 day but only until 3pm"")).

Hope this makes sense, if not let me know and i'll attempt to try and explain my query in more clearly.

Cheers,

Dave

 

[PoeticRobot]

You could use something like:
IF(G301>INT(F301)+1.625,"NO","YES")

Where INT(F301) returns the date of Required Completion and 1.625 is 3pm the following day.

 

[MPaul100]

Something like this should work: IF(G301>(INT(F301)+1+TIME(15,0,0),

 

[Dave2804]

Legend MPaul100! Worked a treat