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

J

jg7804

New Member
Joined
Aug 24, 2018
Messages
2
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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Welcome to the Board!

Try this:
Code: 
=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.
 
Upvote 0
You are welcome!
Glad I was able to help.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Business Hours Mon-Fri 8AM - 5PM between two dates and times
Replies
5
Views
3K
Ganesh1986
G
B
  • Question Question
Sum penalty amount for values that fall outside of a specified range
Replies
6
Views
223
felixstraube
F
D
  • Solved
Timesheet Calculation
Replies
7
Views
1K
jorismoerings
J
L
  • Question Question
Determine match based on if a date range falls within a larger date range :S
Replies
1
Views
409
dermie_72
D
JTL9161
  • Question Question
Hours between two dates
Replies
5
Views
243
MARK858
MARK858

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top