hours and minute calculation

[AJIESPEDAS]

GOOD DAY ALL,
Here i have a problem need you guys help.
a) is it possible to have auto detect on date on column B so that if it changes month, it will automatically merge and put number for the current month as per picture below.
b) from the column e, can it calculate automatically the hours and minute and total up in column M8 as picture, with the column e with custom format cell 0#\:##
c) once all monthly total have been calculated, can it be sum to previous on column c2 to grand total on column I2.

i try before and it seems i cant get it to calculate correctly.

Please help!

regards,

AJIESPEDAS

 

[Jeffrey Mahoney]

Please post XL2BB version of your workbook
XL2BB - Excel Range to BBCode

a) is it possible to have auto detect on date on column B so that if it changes month, it will automatically merge and put number for the current month as per picture below.
b) from the column e, can it calculate automatically the hours and minute and total up in column M8 as picture, with the column e with custom format cell 0#\:##
c) once all monthly total have been calculated, can it be sum to previous on column c2 to grand total on column I2.

a) Merging column A based on change of month would have to be done with a macro
b) That sounds like it's possible. That number format is odd. Waiting for XL2BB version
c) I'm not sure I understand where you want the formula and what you want summed

Jeff

 

[AJIESPEDAS]

Please post XL2BB version of your workbook
XL2BB - Excel Range to BBCode



a) Merging column A based on change of month would have to be done with a macro
b) That sounds like it's possible. That number format is odd. Waiting for XL2BB version
c) I'm not sure I understand where you want the formula and what you want summed

Jeff

hi!, thanks for reply

i upload a picture again for you to understand..hopefully, and ill share a link for the file, is it okay?
the reason for (b) that number format is that to eliminate to always type the colon, so it will automatically colon the HHMM
i hope the picture will explain the (A) & (B)

this link:

HOURS COLLECTION.xlsx

Shared with Dropbox

www.dropbox.com

Regards,
AJIESPEDAS

 

[AJIESPEDAS]

anyone care to help please??

 

[Armstrong_N14]

May not be the exact solution, but Hope it helps

Book1
	A	B	C	D
1	Month	DATE	DAY	MONTHLY TOTAL
2	March	3/7/2023	00:35
3	March	3/21/2023	01:15
4	March	3/23/2023	01:00	02:50
5	April	4/7/2023	01:15
6	April	4/7/2023	00:50
7	April	4/8/2023	00:40
8	April	4/9/2023	00:30
9	April	4/10/2023	00:35	03:50
10	May	5/1/2023	01:15
11	May	5/5/2023	01:00
12	May	5/10/2023	01:15	03:30
13	June	6/1/2023	00:50
14	June	6/21/2023	00:40	01:30
Sheet2

Cell Formulas
Range	Formula
A2:A14	A2	=TEXT(VALUE(B2),"mmmm")
D2:D14	D2	=IF(AND(A2=A1,A2<>A3),SUMIF($A$2:$A$14,TEXT(VALUE(B2),"mmmm"),$C$2:$C$14),"")

 

[AJIESPEDAS]

HI and thanks!,

im quite confuse using your formula, hopefully there is someone else that have an idea to help?

 

[MARK858]

im quite confuse using your formula

Are you saying that the formula is giving the results that you want but you don't understand how it works? The formula doesn't give the results you expected? Or something else?

 

[Dave Patton]

You can try a feature of Excel called Subtotal. Look at Data Subtotals; it will enter the totals and you could then reference the totals.

Initial tiny example

T202309a.xlsm
	A	B	C
1	Month	DATE	DAY
2	March	01-Mar-2023	00:15:00
3	March	05-Mar-2023	00:15:00
4	March	20-Mar-2023	00:15:00
5	April	04-Apr-2023	00:15:00
6	April	05-Apr-2023	00:15:00
7	April	20-Apr-2023	00:15:00
8	May	20-May-2023	00:15:00
9	May	25-May-2023	00:15:00
10	May	30-May-2023	00:15:00
11
3c

Cell Formulas
Range	Formula
A2:A10	A2	=TEXT(B2,"mmmm")
C2:C10	C2	=TIME(0,15,0)

T202309a.xlsm
	A	B	C
1	Month	DATE	DAY
5	March Total		00:45:00
9	April Total		00:45:00
13	May Total		00:45:00
14	Grand Total		02:15:00
3c

Cell Formulas
Range	Formula
C5,C9,C13	C5	=SUBTOTAL(9,C2:C4)
C14	C14	=SUBTOTAL(9,C2:C12)

 

[Dave Patton]

N.B. The forum provides a tool so you can post a concise extract of your sheet to the forum.
It is also useful if you provide the expected results.

Another suggestion that you can try is Sumifs.
You can paste the example to a clean sheet,
Click on the icon below the f(x) in the header. Move to your sheet and click on cell A1 and paste.

T202309a.xlsm
	A	B	C	D	E
1				Mar-2023	00:45:00
2				Apr-2023	00:45:00
3				May-2023	01:15:00
4	Month	DATE	DAY
5	March	01-Mar-2023	00:15:00
6	March	05-Mar-2023	00:15:00
7	March	20-Mar-2023	00:15:00
8	April	04-Apr-2023	00:15:00
9	April	05-Apr-2023	00:15:00
10	April	20-Apr-2023	00:15:00
11	May	20-May-2023	00:30:00
12	May	25-May-2023	00:15:00
13	May	30-May-2023	00:30:00
3c

Cell Formulas
Range	Formula
E1:E3	E1	=SUMIFS($C$5:$C$13,$B$5:$B$13,">="&D1,$B$5:$B$13,"<="&EOMONTH(D1,0))