1/11/2018 00:00 1234
1/11/2018 00:15 1234
.
.
.
2/11/2018 00:00 5678
I just want to take average of time instant 00:00 for whole November. and similarly for all instants so i have an average monthly profile for whole month.
A proper answer requires understanding how Excel stores date and time values. If you select the column with your dates and go to the "Home" tab, on the "Number" ribbon you can change the format of selected cells (in this case, the hole column). Change it from "Date" to "Number" and you will see that this day (Jan 11th, 2018) is always stored as 43111, and that the hours are stored as decimal fractions. This way, 1/11/2018 00:00 is stored as 43111.0000 while 1/11/2018 00:15 is stored as 43111.0104 and so on. This is important because if you want to average the time instant 00:00 for whatever length of date you have, you have to use only the cells that contain an integer value for the date.
We can construct a formula that tests for that. If you go to an empty column and assuming your date is in collum A and line 1, you can write the following formula: =INT(A1)=(A1)
This formula checks if the integer of the cell A1 is equal to that cell itself (in other words, if the date + time in that cell is an integer). This will only happen when the time is 00:00, so this formula will return TRUE if the time for a date is 00:00. You can then apply a filter to your spreadsheet ("Data" tab, "Sort & Filter" Ribbon) and tell Excel to only display lines in which your test was true (in other words, which time was 00:00). Now you can select those values and do the average you want.
Good luck!
A proper answer requires understanding how Excel stores date and time values. If you select the column with your dates and go to the "Home" tab, on the "Number" ribbon you can change the format of selected cells (in this case, the hole column). Change it from "Date" to "Number" and you will see that this day (Jan 11th, 2018) is always stored as 43111, and that the hours are stored as decimal fractions. This way, 1/11/2018 00:00 is stored as 43111.0000 while 1/11/2018 00:15 is stored as 43111.0104 and so on. This is important because if you want to average the time instant 00:00 for whatever length of date you have, you have to use only the cells that contain an integer value for the date.
We can construct a formula that tests for that. If you go to an empty column and assuming your date is in collum A and line 1, you can write the following formula: =INT(A1)=(A1)
This formula checks if the integer of the cell A1 is equal to that cell itself (in other words, if the date + time in that cell is an integer). This will only happen when the time is 00:00, so this formula will return TRUE if the time for a date is 00:00. You can then apply a filter to your spreadsheet ("Data" tab, "Sort & Filter" Ribbon) and tell Excel to only display lines in which your test was true (in other words, which time was 00:00). Now you can select those values and do the average you want.
Good luck!
I think that IF conditional Excel function might be helpful, too
- Badges
- Science topic