I need help writing a formula to return a value between 2 dates and time

[jg7804]

I need to write a formula to return the date between 8am and 8am the next day. So if in cell A1 I have 8/24/2018 7am it would return 8/23 but if I have 8/24/2018 9am it would return 8/24. Any suggestions?

 

[Joe4]

Welcome to the Board!

Try this:

=IF(A1-TRUNC(A1)>(8/24),TRUNC(A1),TRUNC(A1)-1)

The key to this is understand that Excel stores dates as numbers, specifically the number of days since 1/0/1900.
So time is just a fraction of one day. So 8:00 AM is equal to 8/24.

 

[jg7804]

Thank you so much

 

[Joe4]

You are welcome!
Glad I was able to help.