Conditional summing

[Westbury]

I have rows of hourly power data that I need to sum to apply 2 daily charge tariffs. The time date output is in the format 01:00 Mar 08 2018. How can I apply a sumif to add consumption in 2 blocks for each day? the time periods are 07:00 to 19:00 & 19:00 to 07:00 ?

The data is downloaded in a .CSV file which I've saved to xlxs. I've tried to create other columns with time and date separately but Excel won't accept this.

Grateful for any suggestions.

Geoff

 

[James006]

Hi,

My guess would be your initial format ... which most probably ... shows a Text format within your worksheet ...

The first step would be to ensure this format is correctly transformed to a Number : Date Time format ...

HTH

 

[etaf]

are you only interested in time sections date is irrelevant?
as the time is the first part of the string - i suspect excel sees as text - does it see as text OR change to a date/time format ?

depending on the answer to that depends on the approach

so you could extract just the time into a column
but what are you measuring for consumption

you could have a helper column that changes to something you could then SUM - for example DAY or NIGHT

IF ( AND( Time >= 07:00 , TIME < = 19:00 ) , day , night )
tyen sumif the days and nights

I need to know a little more on what excel converts the time/date to
perhaps put a sample on a share like onedrive or dropbox

 

[Kamolga]

Hi,

When I copy-paste 01:00 Mar 08 2018 in A1, it is percieved as text. So if I type

=hour(a1)

I have an error value.
For me the way arround that is to
-go in data tab
-select text to column
-delimited
-next
-space
-next
-Finish

Now I have hours in A1 that can be used as

=if(and(hour(a1)>=7,hour(a1)<19),do this...,or do that...)

 

[etaf]

As its text - it wont do any time or date functions
as it appears on your post - time is always in 24 hr and 5 digit format with leading zeros - so 01:00 or 23:59
then you can use
=TIMEVALUE(LEFT(A1,5))
to actually get a time value

if you just want the hour part
=HOUR(TIMEVALUE(LEFT(A1,5)))

use that in your formula
=if(and(HOUR(TIMEVALUE(LEFT(A1,5)))>=7,HOUR(TIMEVALUE(LEFT(A1,5)))<19),do this...,or do that...)
so that means that 19:00 is not included but 07:00 is



</pre>

 

[Westbury]

Thanks for all of your replies. Yes, the time and date data in the source file shows as General. I've changed this to time & date from Format Cells.

Etaf, the first process here is to identify the relevant tariff for each line of data. I've used the 3rd formula in your post and this gives me the correct tariff identy for the rleavant hour. Thank you! I've used a pivot table to colate the data but months are listed alphabetically; how do i change them to chronologic order?

 

[etaf]

how are you getting to months ?
again if text - excel will only be able to sort as a text item alpha
if a date format - then excel will sort as date months

if its text then you need to convert the text to a month as a date format that excel understands
and of course you would need the year otherwise you will set to this year only

01:00 Mar 08 2018

you would extract the day month and year
=DATEVALUE( MID(A2,11,2) &"/"&MID(A2,7,4)&"/"&RIGHT(A2,4))
I'm UK so using DD/MMM/YYYY format
you may need to change to US , or whatever country you are in

then you can use month() in a pivot table

althought
datevalue on the cell may work on its own

it does on my excel - but then it also recognised the date format anyway

try a text to columns on the file
column with the date - It may just change all the text dates into actual date values imported from the csv

 

[Westbury]

Wayne,

Your formula =DATEVALUE( MID(A2,11,2) &"/"&MID(A2,7,4)&"/"&RIGHT(A2,4)) solves the problem togetherwith getting the cell into dd-mmm-yy format. I now have a meaningful pivot table.

Thank you.

 

[etaf]

you are welcome