0

I need to fill in dates corresponding to time. I just need something that increments the date when the time changes from 23 to 0. Here is what my sheet looks like:

    5/1/2017    23:00
    5/1/2017    23:00
    5/1/2017    23:00
       ?        0:00
       ?        0:00
       ?        0:00

I've tried these with different formats for the time column:

IF(AND(B4=0,B3=23),A3+1,A3)

IF(AND(B4="1/0/1900 12:00:00 AM",B3="1/0/1900 11:00:00 PM"),A3+1,A3)

IF(AND(B4="12:00:00 AM",B3="11:00:00 PM"),A3+1,A3)

Aaron
  • 99
  • 1
  • 9
  • You might want to read through this to better understand dates / times in Excel: http://stackoverflow.com/a/37101358/1153513 This is probably also a good read: http://stackoverflow.com/a/38001028/1153513 Afterwards, I suggest that you make use of the Excel function [Hour()](https://support.office.com/en-us/article/HOUR-function-a3afa879-86cb-4339-b1b5-2dd2d7310ac7) to finalize your fomula(s). – Ralph Nov 07 '17 at 16:06
  • Thank you for the resources – Aaron Nov 07 '17 at 16:15

2 Answers2

0

Your first formula looks correct but you can see where it's wrong if you type =B3=23 into a cell (it's FALSE). Instead, try B3=23/24: =IF(AND(B4=0,B3=23/24),A3+1,A3).

Jared
  • 567
  • 4
  • 10
0
        A           B
1    5/1/2017   11:00:00 PM
2    XXX        12:00:00 AM

Where XXX is = IF(ABS(B1-23/24)<0.001,IF(ABS(B2-24/24)<0.001,A1+1,A1),A1)

what we do here is a nested if statement, basically saying if A and B

where A is "time in cell above is near 11 pm"

and B is "time in cell is near zero"

the way date/times work in excel is that the fractional portion corresponds to time after midnight. So 1/24 ~ .041667 is an hour past midnight, and 23/24 is 11 pm

Mohammad Athar
  • 1,953
  • 1
  • 15
  • 31
  • This makes sense. Thanks for breaking it down. @Jared 's answer is just a little simpler. Would still upvote if I was able. – Aaron Nov 07 '17 at 16:25