Grouping a row of dates and times into weekly averages

[Nevski79]

Hi all,

Newbie to excel here.

I have a row of data that I need to work out daily averages in.

The format for the cell is dd/mm/yyyy 00:00:00 AM or PM ie: 17/07/2018 08:14:33 AM

Is there a formula that I can use to count how many entries per date but then also give me a weekly and daily average?

What I was going to do was a column that returns which date is which day of the week and then calculating the total entries and diving by 4 (4 Saturdays in a month on average etc) but I dont think this is overly accurate.

I was going to use
=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Sidenote: How do I add an if statement to the above formula where if the formula is in a cell where there is no date in A1 that it returns nothing? At the moment, even in blank cells, it's still returning 'Saturday'.

Hopefully this makes sense.

Thanks in advance and hello from Australia!

[Nevski79]

I've just discoverd WEEKNUM. Wondering if that might help?

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[sandy666]

maybe, for the sidenote: =IF(A1="","",CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"))

[Nevski79]

Thanks for the fast response all!

I've attached a dummy workbook. Essentially I need to calculate instances per date and then group them daily.

[FDibbins]

Your dates/times are actually not real dates/times, but text, looking like dates/times (not the end of the world, just saying). Where are these coming from?

Also, what would your expected outcome look like?

[Nevski79]

True. It's an output from a system I am using.

Ideally i would be able to find out how many instances per day and then have it also then arranged in a Sunday to Saturday format with averages per day underneath and then if possible, broken up into weeks as well.

[Nevski79]

Attached is what I have so far (without weekly breakup). Averaging each days result by 4 doesn't seem overly accurate though.

[sandy666]

Honestly I don't know what are you tryin' to do but maybe this (done with PowerQuery aka Get&Transform)
If not I give up

[FDibbins]

I see that your sample file now references another file? Is that the way you will be doing this, or was that just for the sample?

If you have the dates like you showed in your 1st sample, the 1st step is to convert the dates, which you can do like this...
to convert to dates (I take it you dont really need the time?)
highlight the entire range
select Data tab/Text2Columns
click next/next/select Date and select DMY/Finish

[Nevski79]

Sorry for the confusion all. I'll leave this one now as I have enough to go on.

I'll make sure and be clearer next time and include a demo spreadsheet with examples of what I want to achieve.

Thanks again.

[FDibbins]

So what did you settle on?