I am excel stupid

[soxfan9353]

So I need help, I am looking to speed up my time card and have it autofill the dates on my time card when input the weekending date.


I can forward you the spread sheet if you want so you can see what i am working with.

Thanks, John

 

[jtakw]

Hi,

Don't know how your sheet is laid out, may be you can adapt this to your needs:

Book1
	A	B	C	D	E	F	G	H
1	Week Ending
2	8/12/2018	8/5/2018	8/6/2018	8/7/2018	8/8/2018	8/9/2018	8/10/2018	8/11/2018
Sheet176

Cell Formulas
Range	Formula
B2	=$A2-(8-COLUMNS($B2:B2))

B2 formula copied to H2

 

[Legacy 386498]

You can just enter the date in the last cell (for the last day of the week) and enter -1 in each cell before that. For example, last cell is H2, the one before will be =H2-1.

 

[jtakw]

If your "Week Ending" is on the Right side, then all you have to do is select that cell, grab the cell handle, little square at the lower right corner of cell, and drag it across to the left to autofill the dates backwards.

Book1
	A	B	C	D	E	F	G	H
5								Week Ending
6	8/5/2018	8/6/2018	8/7/2018	8/8/2018	8/9/2018	8/10/2018	8/11/2018	8/12/2018
Sheet176

Drag H6 to A6, no formula needed.

 

[jtakw]

Got your message, why don't you post a sample of what you're working on with detailed description of what you want to achieve.

If you must post a sample workbook, you can upload it to a free file hosting site like Dropbox and post the link here.

 

[soxfan9353]

https://drive.google.com/file/d/1fP7_WazJmzPIaq8sYXGNs4DDCkOAjH_w/view?usp=sharing

Here is the workbook

In the top right of the file I want to put the weekending date
Then on the left side under date I would like it to autofill the date starting on monday to sunday

 

[jtakw]

Ok,

For purpose of demonstration, in L4, put in this Sunday's date 8/12/2018

Then in B8, enter this formula:

=L$4-(7-ROWS(B$8:B8))

and copy down column.

Whenever the date changes in L4, Column B will auto populate the dates of the week prior, starting with Monday, leading up to the date in L4.

I suggest you remove the personal info and re-upload your file.

Let me know how this works for you.

 

[soxfan9353]

I believe it worked but on saturday and sunday of the week i am getting a 0:00 at the end of the date

 

[jtakw]

That's odd, I'm not getting that, just the Dates, using your upload sample.

Are you using the same file as the one you uploaded?
May be change Column B formatting to Date.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	NAME:							WEEK ENDING:			8/12/2018
5
6		JOB	START	END	COOK #134		DUPAGE #701		LAKE #150		WILL # 176
7	DATE	NAME	TIME	TIME	R	OT	R	OT	R	OT	R	OT	MILEAGE	EXPENSES
8	8/6/2018
9	8/7/2018
10	8/8/2018
11	8/9/2018
12	8/10/2018
13	8/11/2018
14	8/12/2018
Sheet1

Cell Formulas
Range	Formula
B8	=L$4-(7-ROWS(B$8:B8))

 

[soxfan9353]

I dont know how to upload it like you did