If a date (long) in one cell = ? then adjacent cell = date (long)

randaza1

New Member
So, and yes I can stand this, I get paid on Tuesdays and Fridays. I am a self-employed retired person, and really need to keep track of these days of pay.
I am constructing my spread sheet as I have shown below.
Here is the schedule on how its paid:
SUNDAY, MONDAY, FRIDAY, and SATURDAY work, is paid on TUESDAY's
TUESDAY, WEDNESDAY, THURSDAY work, is paid on FRIDAY's

The process is pretty simple, You manually input a date in A1, and B1 will contain the formula to output which date (all in long form as it shows below)

- If the entry in A1, as stated above, comes back as a Sunday, Monday, Friday, or Saturday, Column B needs to show the first Friday following that day. (see A2, and A3 below)​
- If the entry in A1, as stated above, comes back as a Tuesday, Wednesday or Thursday, Column B needs to show the first Tuesday following that day (see A1, and A4 below)​
I have tried understanding the IF functions, but brain freeze. The =sum(a1+2) works, but when I copy it down, well you guessed it, it changes the row, but keeps the 2 the same.​
If you wouldn't mind assisting, I would appreciate it.​
~Tony​

 A < manual input >​ B < formula and output column>​ 1 Wednesday, January 01, 2020 Friday, January 03, 2020 2 Friday, January 03, 2020 Tuesday, January 07, 2020 3 Saturday, January 04, 2020 Tuesday, January 07, 2020 4 Tuesday, January 07, 2020 Friday, January 10, 2020

Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

tyija1995

Well-known Member
Hey, I think your description is the wrong way round
So, and yes I can stand this, I get paid on Tuesdays and Fridays. I am a self-employed retired person, and really need to keep track of these days of pay.
I am constructing my spread sheet as I have shown below.
Here is the schedule on how its paid:
SUNDAY, MONDAY, FRIDAY, and SATURDAY work, is paid on TUESDAY's
TUESDAY, WEDNESDAY, THURSDAY work, is paid on FRIDAY's

The process is pretty simple, You manually input a date in A1, and B1 will contain the formula to output which date (all in long form as it shows below)

- If the entry in A1, as stated above, comes back as a Sunday, Monday, Friday, or Saturday, Column B needs to show the first Friday following that day. (see A2, and A3 below)​
- If the entry in A1, as stated above, comes back as a Tuesday, Wednesday or Thursday, Column B needs to show the first Tuesday following that day (see A1, and A4 below)​
I have tried understanding the IF functions, but brain freeze. The =sum(a1+2) works, but when I copy it down, well you guessed it, it changes the row, but keeps the 2 the same.​
If you wouldn't mind assisting, I would appreciate it.​
~Tony​

 A < manual input >​ B < formula and output column>​ 1 Wednesday, January 01, 2020 Friday, January 03, 2020 2 Friday, January 03, 2020 Tuesday, January 07, 2020 3 Saturday, January 04, 2020 Tuesday, January 07, 2020 4 Tuesday, January 07, 2020 Friday, January 10, 2020

I think your description is the wrong way around there (Where you put -if the entry in A1...)

so I have wrote both ways below, choose which applies (I think it's the latter as it matches your post image, but oposes your post description!)

Book1
AB
1Wednesday, January 01, 2020Tuesday, January 07, 2020
2Friday, January 03, 2020Friday, January 10, 2020
3Saturday, January 04, 2020Friday, January 10, 2020
4Tuesday, January 07, 2020Tuesday, January 14, 2020
5
6
7Wednesday, January 01, 2020Friday, January 03, 2020
8Friday, January 03, 2020Tuesday, January 07, 2020
9Saturday, January 04, 2020Tuesday, January 07, 2020
10Tuesday, January 07, 2020Friday, January 10, 2020
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=IF(WEEKDAY(A1,15)<=4,A1+(8-WEEKDAY(A1,15)),A1+(8-WEEKDAY(A1,12)))
B7:B10B7=IF(WEEKDAY(A7,15)>4,A7+(8-WEEKDAY(A7,15)),A7+(8-WEEKDAY(A7,12)))

randaza1

New Member
tyija1995
Yes, I did make an error...
I looked at your formulas and I agree it would be the latter (b7:b10) problem is my B column output is #NUM!

tyija1995

Well-known Member
How is your column A formatted?
Can you try =ISNUMBER(A1) and what does it return? If TRUE then the formulas provided should work, if FALSE then I think it is down to the formatting, as you want actual dates (serial numbers) in column A, you will want to enter the date (say 1st jan 2020) as 01/01/2020

You can format cells afterwards as "dddd, mmmm dd, yyyy" and it will display as "Wednesday, January 01, 2020" but it will be a serial number in Excel now.

randaza1

New Member

reports back as TRUE

randaza1

New Member
By the way, I have MS EXCEL 2007

tyija1995

Well-known Member

Right then it is a version issue, just read Weekday function added extra parameters for the return_type in Excel 2010 onwards.

Book1
AB
1Wednesday, January 01, 2020Friday, January 03, 2020
2Friday, January 03, 2020Tuesday, January 07, 2020
3Saturday, January 04, 2020Tuesday, January 07, 2020
4Tuesday, January 07, 2020Friday, January 10, 2020
Sheet2
Cell Formulas
RangeFormula
B1:B4B1=IF(AND(WEEKDAY(A1,2)<5,WEEKDAY(A1,2)>1),A1+(5-WEEKDAY(A1,2)),A1+(9-WEEKDAY(A1,2)))

randaza1

New Member
Wow, thank you so very much. So it gets down to the different versions. I currently have a windows 10 upgrade on my pc, but honestly it keeps crashing on me. I am thinking of going back to 7
Again I do appreciate your assistance WORKS LIKE A CHARM

tyija1995

Well-known Member
No problem, actually after testing it falters on Monday's, it actually leaps on a Monday to the Tuesday 8 days ahead, woops!

Try this revised formula which fixes Monday's:

Book1
AB
1Wednesday, January 01, 2020Friday, January 03, 2020
2Friday, January 03, 2020Tuesday, January 07, 2020
3Saturday, January 04, 2020Tuesday, January 07, 2020
4Tuesday, January 07, 2020Friday, January 10, 2020
Sheet2
Cell Formulas
RangeFormula
B1:B4B1=IF(AND(WEEKDAY(A1,2)<5,WEEKDAY(A1,2)>1),A1+(5-WEEKDAY(A1,2)),IF(WEEKDAY(A1,2)<>1,A1+(9-WEEKDAY(A1,2)),A1+1))

Essentially I just add on a day if it's a Monday.

Yep that worked

Replies
3
Views
92
Replies
2
Views
30
Replies
18
Views
192
Replies
2
Views
82
Replies
3
Views
132

1,126,888
Messages
5,621,426
Members
415,839
Latest member
Pollydooner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back