Need some help with dates

[vanzie]

Good day everyone

I have excel knowledge but im quite out of practise. I have attached a spread sheet where we do simple and quick statistics at my office.

What i would like to know is if i enter a date range for a week, is there a possible way to have designated column cells display the individual day number for that week?

Refering to the attached sheet, the week dates will be entered in E2. The day numbers to auto populate will be C5:I5. Or is there another way to work with dates so i can make one work book for the entire year?

The reason why i ask this is because there are more than one person that modifies the totals etc. (each shift must add their statistics for the day or the week) and they completely messed up my design by adding rows and columns and having the columns have different sizes so i would like to restrict them from changes too much of the existing sheet format. They basically just have to enter their numbers and the week date. Nothing more.

Any help or advice will be appreciated.

[alansidman]

If you put the start date in E2. Make sure the cell is not merged with any other.
Then in C5 =E2, In D5 =C5 +1 and copy this to the right

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
2	WEEKLY REPORT FROM:				7/3/2020	7/9/2020
3	JUNE 2020 OPERATIONS
4	Duties		DAY OF THE WEEK							TOTAL
5			3	4	5	6	7	8	9
6										0

Sheet: Sheet1

[ORoos]

Hi vanzie,
Welcome to the forum.
If you split your date in E2 to the start date of the week. You then can easily calculate the end date (E2+7).
In cell C5 enter =Day(E2) then pull across. This will give you the date number.

If you have issues with others adding/deleting columns/rows, consider locking the worksheet. Tick all (allow the user) except Insert Column, Insert Row, Delete Column, Delete Row. If you further want to prevent users to hide/unhide columns/rows or changing the row height/column widths. untick Format Columns, Format Rows.

If you then also want to protect the formula in column J (or elsewhere), you can lock them too, so only cells in A6:I31 can be entered.

Trust this helps.

[vanzie]

alansidman Your formula works fine. The only problem i get is when it comes to month end crossing over into the new month. Our stats report date goes from each Friday to the next week Thursday. So the 31st July falls on a Friday. If i use your formula and the end date of my start date is somewhere in August, it wont restart at 1. It will count on from 31 to 32,33,34 etc.

ORoos Your formula also works fine for me. The one problem i noticed is if i have a start date and an end date and i use Day(E2) in cell C5 and pull it across, the following cells C6,C7 etc formula shows the next cells of E2. So C5 will show Day(E2) but then C6 shows Day(E3) and C7 shows Day(E4). I'll see if it works to lock the start date in the formula with the F4 key. What do you think?

[vanzie]

okay so i might have found a good one to work. Tested it on months ending at 31 days an others ending on 30 also months such as February with 29 days. Ive locked the start date in the C5 cell and added +0. The next cell, C6, will have +1 after

=DAY($E$2+0) that worked out great.

[ORoos]

Yep that looks like it is working perfectly. Glad to hear you worked it out.
If this answers your original question, please mark the thread as 'Solved'.
Thanks