# Calculate Date between Two Different Time Stamps

1. ## Calculate Date between Two Different Time Stamps

Need help in writing the formula to find the date between two different time period

Example :
If the B2 value is between 6/1/18 8:00 AM to 6/2/18 8:00 AM then need C2 to show date at 6/1/18
similarly if B2 value is between 6/2/18 8:00 AM to 6/3/18 8:00 AM then need C3 to show date as 6/2/18

2. ## Re: Calculate Date between Two Different Time Stamps

This looks like it's working

=B2-(MOD(B2,1) < TIME(8,0,0))

Depends what results you want for other values

3. ## Re: Calculate Date between Two Different Time Stamps

Try

=B2-1/3 formatted as date, or
=INT(B2-1/3) if you don't want the time portion

4. ## Re: Calculate Date between Two Different Time Stamps

So i have the date and time stamps in column C... need the results as in Column B ( i have manually entered it now).. change in date required refer to Row 8

1 18/06/2018 16/06/2018 16:15
2 18/06/2018 17/06/2018 16:30
3 18/06/2018 18/06/2018 16:45
4 18/06/2018 19/06/2018 17:00
5 18/06/2018 19/06/2018 17:15
6 18/06/2018 19/06/2018 17:30
7 18/06/2018 19/06/2018 17:45
8 19/06/2018 19/06/2018 18:00
9 19/06/2018 19/06/2018 18:30
10 19/06/2018 19/06/2018 18:45
11 19/06/2018 19/06/2018 19:00
12 19/06/2018 19/06/2018 19:15
13 19/06/2018 19/06/2018 19:30
14 19/06/2018 19/06/2018 19:45

5. ## Re: Calculate Date between Two Different Time Stamps

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

6. ## Re: Calculate Date between Two Different Time Stamps

Thanks Sandy, i have attached the sheet as advised.. Before and After sheet with expected results color coded the cells for easy identification

7. ## Re: Calculate Date between Two Different Time Stamps

jacobraj1831 I have a question regarding B2 in the upload and post #1:
If the B2 value is between 6/1/18 8:00 AM to 6/2/18 8:00 AM then need C2 to show date at 6/1/18
B2:C2 is 5/31/2018 and 6/1/2018 17:30

The time/date slot C2 is between 6/1/18 8:00 AM and 6/2/18 8:00 AM. So why is B2 5/31/18 and not 6/1/18?

What am I missing?

There are currently 1 users browsing this thread. (0 members and 1 guests)