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

randaza1

New Member
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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>
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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>
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

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
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

reports back as TRUE
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Try this instead:

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
Joined
Mar 4, 2008
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,025
Messages
5,628,192
Members
416,300
Latest member
Kxs00301

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top