IF Formula for Time and Date

[AmanE]

Is there a formula to acheive the following:

If I have these transactions:
Cell
<TABLE style="WIDTH: 131pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=175><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20 width=64>A1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl106 width=111 align=right>21/06/2011 00:05</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>A2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl106 align=right>21/06/2011 05:00</TD></TR></TBODY></TABLE>

I want to show that if the time for cell A1 is past midnight and between the hours of 05:00 then the date will still show the previous days i.e. 20/06/2011. I've tried using a IF statement combined with TIME and Date but doesn't really give me what I need. Any help much appreciated.
Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Why do you need to deduct a day exactly?

 

[adam087]

Does this work?
=IF(MOD(A1, 1)<(5/24), A1-1, A1)

Regards
Adam

 

[ArfArfArf]

Adam087's reply still keeps the time value attached to the date/time value, i.e. 24hrs earlier. That might be what you need, but perhaps something like

=IF((A1-INT(A1))-"05:01"<=0,"Day Before","Today") might suit you?

Using "05:00" doesn't catch 5am, hence "05:01", prob something to do with precision of time values (Excel hold dates and times as a number - e.g. 40953.5 is today at midday, but 5am is 40953.20833333333)

 

[AmanE]

Thats Great - both sollutions acheive what I need to.

Thanks for your responses.