HELP: Excel Date in Column based on Value in another column

[JayhawkSS1LE]

I need some excel help... Column B is Date and Column C in Time. Down Column C is listed:
0:00
0:30
1:00...
.
.
.
23:30
0:00
0:30

Essentially what I need in Column B (Date) is the date to be the same from time 0:00 thru 23:30 and then when 0:00 hits in Column C the date increase by 1 day to the next date. I have 6 months worth of data that is in 30 minute increments such as this, that I need to assign dates to. The first date is April 1, 2017 listed in the cell as 2017-01-04

Any help would be appreciated!

Thanks

[kmarshall2121]

at the beginning of the c cell, type '=XXX+1'
xxx is the last cell you want to add one to... If I am understanding what you are trying to do anyway... dont forget to format the new cell to the 'date' format

[Steve Dallas]

First, you can put two dates that are the same in column B, then highlight both of those dates. If you then click on the lower right corner of that selection and drag down to where the 23:30 time is, that will autofill all the dates so they are all the same.

Unfortunately, the autofill isn't intelligent enough to autofill for 6 months like that. You can fill blocks of one day at a time that way, which might speed things up.

That is the non-programming way to do this.

Then, there is using VBA to do this. I'm not sure what version of Excel you are using...and that might make things a little different.

Essentially, you can write a script that will fill a column with the dates, incrementing the date by one every 48 cells, until the date reaches the date you need to stop.

[Zzzato]

This is the easiest way to this.
In your first three date cells block erase it, input these values starting at April 1 00:00 -01:00.





Grab the three blocks drag them down to the end and change the format to short date or whatever date format you want. All this is doing is converting the time and date to a serial number and then your converting it back to what you want.

[JayhawkSS1LE]

Originally Posted by Steve Dallas

Essentially, you can write a script that will fill a column with the dates, incrementing the date by one every 48 cells, until the date reaches the date you need to stop.

Thank you. Yes, this is exactly what I need to do is increase the date by 1 every 48 cells...

I am dealing with almost 8800 rows of data on this sheet and I have 70 similar sheets. I definitely need some formula

[JayhawkSS1LE]

Originally Posted by Zzzato

This is the easiest way to this.
In your first three date cells block erase it, input these values starting at April 1 00:00 -01:00.





Grab the three blocks drag them down to the end and change the format to short date or whatever date format you want. All this is doing is converting the time and date to a serial number and then your converting it back to what you want.

Thank you, the date doesnt change when it gets to the next 0:00 Time.

I need the date to change when it hits the 0:00 time in Column C, like this (but the same date needs to carry down until the next 0:00 time and then increase the date by 1)

[Steve Dallas]

What version of Excel are you using? I use 2010, so I could write you a VBA script in that...

[JayhawkSS1LE]

Here is the answer! Thanks for all that helped!

=DATEVALUE("2017-04-01")+1*(INT(ROW(B49)/48)-1)

[Steve Dallas]

Awesome.

VBA scripting can be very handy, if you ever want to dabble in it.

[jrhagen]

Use this.

[Zzzato]

Originally Posted by JayhawkSS1LE

Thank you, the date doesnt change when it gets to the next 0:00 Time.

I need the date to change when it hits the 0:00 time in Column C, like this (but the same date needs to carry down until the next 0:00 time and then increase the date by 1)

Weird mine totally did.