Forum Discussion

Aditya Jadhav's avatar
Aditya Jadhav
Brass Contributor
Feb 13, 2018

How to add the dates using the time criteria ?

Hi,   Want to add dates based on the time criteria which should be done automatically as the data flows in that is a bit tedious to do manually. Have time from 00:00 hrs to 23:00 and that can be ...
Excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 13, 2018

    Hi Aditya,

     

    Not sure what is the actual problem. In general that could the formula with structured references which will be filled automatically, like

    =IFERROR(OFFSET([@Date],-1,0)+IF([@Time]<OFFSET([@Time],-1,0),1,0),DATEVALUE("2016-04-01"))

    Here we check if next time is less than previous time since your time not always starts from 00:00. Second, in your table dates are not always sequential, e.g. Oct 28, 2017 is right after Oct 26, 2017. Not sure that is mistake or some logic is behind. 

     

    Anyway, with structured references not necessary to copy/paste cells, better to use them.

Aditya Jadhav's avatar
Aditya Jadhav
Brass Contributor
Dear willy,
I don't want the date and time to combine together. I want the date to remain constant in the column until the day changes.
For e.g; 02-02-2018 is the date in the first column and the second column contains time from 00:00 hrs to 23:00 hrs. Now i need some way to fill the dates until 2300 hrs and then the date should change. How to do that.

Regards,
Aditya
Willy Lau's avatar
Willy Lau
Steel Contributor
Feb 13, 2018

You put the first date value in the first row.

Then, starting from the second row, the first column use the following formula

= IF (MOD($B2,1) = 0, $A1 +1, $A1)
  • Aditya Jadhav's avatar
    Aditya Jadhav
    Brass Contributor
    Feb 13, 2018
    Yes this helps for the dates to get changed quickly but please look into the attached sheet you will get to know the actual issue which i'm talking about. This helps but not completely to my problem.
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Feb 13, 2018

      Hi Aditya,

       

      Not sure what is the actual problem. In general that could the formula with structured references which will be filled automatically, like

      =IFERROR(OFFSET([@Date],-1,0)+IF([@Time]<OFFSET([@Time],-1,0),1,0),DATEVALUE("2016-04-01"))

      Here we check if next time is less than previous time since your time not always starts from 00:00. Second, in your table dates are not always sequential, e.g. Oct 28, 2017 is right after Oct 26, 2017. Not sure that is mistake or some logic is behind. 

       

      Anyway, with structured references not necessary to copy/paste cells, better to use them.

      • Aditya Jadhav's avatar
        Aditya Jadhav
        Brass Contributor
        Feb 14, 2018
        Dear @sergei
        Thank you so much for the solution.
        Hope I have not asked too much.

        Regards,
        Aditya
    • Willy Lau's avatar
      Willy Lau
      Steel Contributor
      Feb 13, 2018

      A2

      =DATE(2016,4,1)

      B2

      =MOD(1/24 * MOD(ROW() - 2, 24), 1)

      A3

      =IF($B3 = 0, $A2 + 1, $A2)

      B3

      =MOD(1/24 * MOD(ROW() - 2, 24), 1)

       

      copy A3:B3, then, should be stay at A3:B3

      press Shift + End

      keep pressing Shift, + Ctrl + Down Arrow

      Ctrl + V to paste it

       

      Select All (Click the corner)

      Copy

      Right click at the corner

      Paste as Value

Resources