Forum Discussion
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 filled out easily but the dates are messing up. My first column has dates and the second has time now i want to fill the date for 00:00-23:00 hours by a same date and then the date should change and keep on going, how can i do that ?. please guide me with this.
Attaching the file.
Regards,
Aditya
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.
Do you want something like this
=INT($A1) + MOD($B1,1)
- 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,
AdityaYou 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)
