Excel Help and Problems

[cgr]

How can I get half-hourly data which looks like this
 

2/1/2019 0:30	402.48
2/1/2019 1:00	399.6
2/1/2019 1:30	392.4
2/1/2019 2:00	386.64
2/1/2019 2:30	374.4
2/1/2019 3:00	373.68
2/1/2019 3:30	365.04
2/1/2019 4:00	369.36
2/1/2019 4:30	381.96

converted into hourly data formatted like this

11/1/2018	1	166.32
11/1/2018	2	147.96
11/1/2018	3	144.36
11/1/2018	4	146.16
11/1/2018	5	153.72
11/1/2018	6	170.64
11/1/2018	7	176.04
11/1/2018	8	179.28
11/1/2018	9	178.56
11/1/2018	10	164.16
11/1/2018	11	130.68
11/1/2018	12	138.24
11/1/2018	13	135

or this
 

11/1/18 1:00	166.32
11/1/18 2:00	147.96
11/1/18 3:00	144.36
11/1/18 4:00	146.16
11/1/18 5:00	153.72
11/1/18 6:00	170.64
11/1/18 7:00	176.04
11/1/18 8:00	179.28
11/1/18 9:00	178.56
11/1/18 10:00	164.16
11/1/18 11:00	130.68
11/1/18 12:00	138.24
11/1/18 13:00	135

Try:
=MROUND(A1,1/24)
Change A1 to the cell containing your data, and double click the fill handle to repeat the formula for all your instances.

[aygart]

What are those numbers following the time? And are the in the same cell?

One cell is date and time the other is the value. I don't care if in the end result the date and time are separated as long as the end result has the total value of each hour added together.

[skyguy918]

One cell is date and time the other is the value. I don't care if in the end result the date and time are separated as long as the end result has the total value of each hour added together.

https://www.excelcampus.com/charts/group-times-in-excel/

[aygart]

https://www.excelcampus.com/charts/group-times-in-excel/

These examples are counting transactions. The more similar example with their data would be to get the sum of the dollar amount per hour. I was able to get hours summed together using PivotTable but it is grouping the hours differently from the way the labeling intends. It also doesn't give the date and time back in the way I need it. I am going to try playing around with the concepts here though. Thanks!

[the great]

Anyone know if it’s possible to make part of a formula to come out underlined?

[etech0]

Anyone know if it’s possible to make part of a formula to come out underlined?

I believe not, unless maybe with VBA

[cgr]

One cell is date and time the other is the value. I don't care if in the end result the date and time are separated as long as the end result has the total value of each hour added together.

I'm not sure what those values are, but if you can separate them from the date & timestamp, the below should work. (use Data>Text To Columns)

Try:
=MROUND(A1,1/24)
Change A1 to the cell containing your data, and double click the fill handle to repeat the formula for all your instances.

Just make sure that your cells are formated correctly (under Format Cells> Number tab> Data Section> Choose the type that returns d/mm/yy 00:00).

[aygart]

I'm not sure what those values are, but if you can separate them from the date & timestamp, the below should work. (use Data>Text To Columns)


Just make sure that your cells are formated correctly (under Format Cells> Number tab> Data Section> Choose the type that returns d/mm/yy 00:00).

It seems to me that you think I am trying to change the 30 hours to be rounded up and do not care about the values. That is not the case. I am looking for a sum of the values for each hour.

[cgr]

It seems to me that you think I am trying to change the 30 hours to be rounded up and do not care about the values. That is not the case. I am looking for a sum of the values for each hour.

Sorry, don't know what those values represent.

[aygart]

Sorry, don't know what those values represent.

They represent the amount of kWh of electricity used over that half hour.

[SrulyS]

It seems to me that you think I am trying to change the 30 hours to be rounded up and do not care about the values. That is not the case. I am looking for a sum of the values for each hour.

You're trying to eliminate the half hour rows while adding their values (from the rightmost column) to the row above/below?

[cgr]

Anyone know if it’s possible to make part of a formula to come out underlined?

Possible with VBA only

[cgr]

They represent the amount of kWh of electricity used over that half hour.

Ok. Got it now.
2 options:
1) follow my instructions as before, copy paste values so that excel actually changes the half hour values to hourly, and use a pivot table to sum for each hour
2) separate date/time value and kwh into 2 columns, sum every second row (use fill handle), copy paste summed values (text only- not formulas) back into original kwh column, sort so that data with empty columns can be easily deleted.

[aygart]

You're trying to eliminate the half hour rows while adding their values (from the rightmost column) to the row above/below?

Correct.

[aygart]

Ok. Got it now.
2 options:
1) follow my instructions as before, copy paste values so that excel actually changes the half hour values to hourly, and use a pivot table to sum for each hour
2) separate date/time value and kwh into 2 columns, sum every second row (use fill handle), copy paste summed values (text only- not formulas) back into original kwh column, sort so that data with empty columns can be easily deleted.

These are the directions I have been going. Pivot table does it without needing to change the half hour values. I need to tweak it though to group propery and to align them with a date and time cell to input properly.
2 is what I have been thinking but didn't test yet. I have not been successful in getting the fill handle to fill only every other row, but I thought of some work arounds which I did not get to yet.

[cgr]

These are the directions I have been going. Pivot table does it without needing to change the half hour values. I need to tweak it though to group propery and to align them with a date and time cell to input properly.
2 is what I have been thinking but didn't test yet. I have not been successful in getting the fill handle to fill only every other row, but I thought of some work arounds which I did not get to yet.

For the fill handle, fill in the first 4 instances yourself, and then excel will automatically understand what you mean when you double-click it to fill down.

[etech0]

Or you  can wrap your function in IF(MOD(celladdress,2)=0,restofformula,"") to return blank if the cell row # is odd (or adjust it for even rows)

[SrulyS]

I have not been successful in getting the fill handle to fill only every other row, but I thought of some work arounds which I did not get to yet.

For the fill handle, fill in the first 4 instances yourself, and then excel will automatically understand what you mean when you double-click it to fill down.

Or you  can wrap your function in IF(MOD(celladdress,2)=0,restofformula,"") to return blank if the cell row # is odd (or adjust it for even rows)

Just enter the formula once, select formula and one blank cell, and then fill down, it will only fill every other row.

2) separate date/time value and kwh into 2 columns, sum every second row (use fill handle), copy paste summed values (text only- not formulas) back into original kwh column, sort so that data with empty columns can be easily deleted.

I was going to suggest something similar but (I believe) a little easier.  If you are doing this regularly I suggest using keyboard shortcuts and with my method you will not need to switch selected range once you fill down the formula so it should be quicker. Once you get used to this shortcut sequence it will be really quick.

Sum every second row (make sure to put formula in x:00 rows,and if you want 1:30 with 1:00 make sure to sum the proper rows, etc..). Just enter the formula once, select formula and one blank cell, and then fill down, it will only fill every other row.

Then with the cells still selected you can do the following:
Ctrl+C
Left Arrow
Alt+H, V, V                        (Paste Values in original column)
Alt+H, FD, S, K, Enter       (Select blank cells from current selection [if you are doing this without shortcuts, it's Find & Select, Go to Special, Blanks])
Alt+H, D, R,                     (Delete rows of selected cells)
Right Arrow
Alt+H, D,C                        (Delete formula column)  [Ctrl+Space, Ctrl+Minus is probably better for this step]

[Der Deutsche Jude]

Hi all, anyone with familiarity with Excel Templates that has a solution to this problem?

I have a Word document with a hyperlink to an Excel Template (.xltx) but when clicked, it opens the original file, not a new instance of it. How do I get it to open the same as when you would open it from the file browser or from a shortcut?

Thanks.

[etech0]

Maybe try saving the template as read-only?