Add duration based on selected date range

[Eric Livesay]

Hello all and prior thanks for any assistance given!

I have a spreadsheet for tracking volunteer hours. I have a column for that days date (formatted YYYY, Mmm, D). And another column that shows the duration of their volunteer hours based on time in and time out columns.

I would like to be able to have a cell where I can type or select a specific month and year, then have another cell add up the work hours just for that month. Can't figure it out.

I can upload a mock spreadsheet if necessary.

 

[etaf]

if you put the month into a cell , as say 1/1/25 - so JAN
SUMIFS( column for hours , Dates column , ">="&Date cell, Dates column , "<="&EOMONTH(Date cell, 0))
format the cell with the sumif as [H]:mm

a mockup would help

but heres one i made

Book2
	A	B	C	D	E	F
1	date		Hours		MONTH =	2/1/24
2	1/1/24		1:02
3	2/1/24		2:10		TOTAAL	30:18:00
4	3/1/24		3:18
5	4/1/24		4:26
6	5/1/24		5:34
7	6/1/24		6:42
8	7/1/24		7:50
9	1/1/24		8:58
10	2/1/24		10:06
11	3/1/24		11:14
12	4/1/24		12:22
13	5/1/24		13:30
14	6/1/24		14:38
15	7/1/24		15:46
16	1/1/24		16:54
17	2/1/24		18:02
18	3/1/24		19:10
19	4/1/24		20:18
20	5/1/24		21:26
21	6/1/24		22:34
22	7/1/24		23:42
23
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUMIFS(C2:C22,A2:A22,">="&F1,A2:A22,"<="&EOMONTH(F1,0))

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Eric Livesay]

deleted

 

[Eric Livesay]

I've never done a mini sheet before but here goes:

leena formula.xlsx
	A	B	C	D	E	F
1	Name:	Bandy, Matt	Notes
2	Phone Number:
3	Emergency Contact:
4	Emergency Contact Phone Number:
5	Relationship To Emergency Contant:
6			Enter date in "Month Query" cell. Ex: 2024 feb. Then the cell under "total month hours" shows the total number of hours worked for that month.
7	Date	Time In	Month Query	Total Month Hours	Tasks/#	Staff Initial
8			2024 feb
9			Time Out	Total Time
10	CTRL + ; to insert today's date	CTRL + ; to insert today's date	CTRL + ; to insert today's date
11	2024, Jan 8	4:00:00 PM	6:00:00 PM	02:00:00	Adult Education Workshop	ML
12	2024, Jan 9	4:00:00 PM	6:00:00 PM	02:00:00		ML
13	2024, Jan 22	4:00:00 PM	6:00:00 PM	02:00:00		ML
14	2024, Jan 29	4:00:00 PM	6:00:00 PM	02:00:00		ML
15	2024, Jan 30	4:00:00 PM	6:00:00 PM	02:00:00		ML
16	2024, Feb 5	4:00:00 PM	6:00:00 PM	02:00:00		ML
17	2024, Feb 6	4:00:00 PM	6:00:00 PM	02:00:00		ML
18	2024, Feb 12	4:00:00 PM	6:00:00 PM	02:00:00		ML
19	2024, Feb 13	4:00:00 PM	6:00:00 PM	02:00:00		ML
Sheet1

 

[etaf]

OK,
The date column has not come across as real dates , but text
are they real dates ?

I have a column for that days date (formatted YYYY, Mmm, D).

I have changed to real dates and formatted as YYYY, MMM, D

=SUMIFS(D10:D100,A10:A100,">="&C7,A10:A100,"<="&EOMONTH(C7,0))

Book3
	A	B	C	D	E	F
1	Name:	Bandy, Matt	Notes
2	Phone Number:
3	Emergency Contact:
4	Emergency Contact Phone Number:
5	Relationship To Emergency Contant:
6	Date	Time In	Month Query	Total Month Hours	Tasks/#	Staff Initial
7			2024, Feb	8:00
8			Time Out	Total Time
9	CTRL + ; to insert today's date	CTRL + ; to insert today's date	CTRL + ; to insert today's date
10	2024, Jan 08	16:00	18:00	2:00	Adult Education Workshop	ML
11	2024, Jan 09	16:00	18:00	2:00		ML
12	2024, Jan 22	16:00	18:00	2:00		ML
13	2024, Jan 29	16:00	18:00	2:00		ML
14	2024, Jan 30	16:00	18:00	2:00		ML
15	2024, Feb 05	16:00	18:00	2:00		ML
16	2024, Feb 06	16:00	18:00	2:00		ML
17	2024, Feb 12	16:00	18:00	2:00		ML
18	2024, Feb 13	16:00	18:00	2:00		ML
19
Sheet1

Cell Formulas
Range	Formula
D7	D7	=SUMIFS(D10:D100,A10:A100,">="&C7,A10:A100,"<="&EOMONTH(C7,0))

will be on dropbox for a few days

Dropbox

www.dropbox.com

 

[Eric Livesay]

Thanks a ton! I have to get the dates formatted correctly, but this will work great.
You don't happen to know how to change the date format for an entire workbook do you. The workbook I'm trying to fix has several tabs at the bottom for letters of last names (A-Z). There are several names on each tab. Each date column has different date formats. I don't want to have to go through and select each range manually and then change the format.
But regardless, thanks again!

 

[etaf]

you are welcome
No, I dont know how to do that - other member may and even have some VBA code to do that

Worth starting a new question/thread asking that

Also its worth updating your profile with the version of excel you are using - as new versions have a lot of new functions