Extract from Date and Time

[jani.pk]

Dear Members,
I have attach a sheet for ease of reference upon which i need the help from you great people

The colum A has the complete date and time and what i need is to spread this in a table where it shows month wise and the date wise the hours worked. i have draw the example from coloum B.

let me know incase of any query.

thankyou

[sandy666]

what does that mean:
Hours: 8 7 6 8 9 3 6 7

[jani.pk]

this is just sample.
here i need the actual hours that worked from coloum A on that specified date.

[sandy666]

It is not clear
How do you want to count hours?
last date of month - first date of month?
more details please....

prepare real result manually

[jani.pk]

i have attach the sheet.

[sandy666]

ok, thanks

[sandy666]

Are you able to use PowerQuery (add-in for Ex2010 Pro Plus from MS site)
and you really want this table horizontal?

[jani.pk]

no it is not necessary in form of horizontal. i have excel 2016 installed.
it would be more convenient if it is solved from any formula as i am working in my officer where any installation is blocked.

[sandy666]

so update your profile about Excel version

I can do this with PowerQuery, sorry

maybe someone else will give you formula solution

btw. Excel 2016 has PowerQuery built-in, so if you have in your office Ex2016 or higher it will not be a problem

but here is example:

[jani.pk]

ok thanks
can you please guide how can i use power query to get this result.

[Glenn Kennedy]

We do not need 11,000 rows to come up with a solution. In C2, copied across:

=IFERROR(AGGREGATE(14,6,$A$2:$A$110/(INT($A$2:$A$110/1)=C$1),1)-AGGREGATE(15,6,$A$2:$A$110/(INT($A$2:$A$110/1)=C$1),1),"")

I deleted most of them to make a sample sheet. Given that you're in France, you'll need ; and not , in the formula

[jani.pk]

Great Glenn. Superb

[Glenn Kennedy]

You're welcome and thanks for the rep.

[AliGW]

Jani.pk - please update your forum profile to reflect the version of Excel you are actually using.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.