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

R

randaza1

New Member
Joined
Mar 4, 2008
Messages
48
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Hey, I think your description is the wrong way round
randaza1 said:
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
Click to expand...

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)))
 
Upvote 0
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! :(
 
Upvote 0
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.
 
Upvote 0
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
How to get nearest dates for tues and fri
Replies
5
Views
237
felixstraube
F
J
  • Solved
COUNTA, remove null or blanks from count
Replies
8
Views
233
Fluff
Fluff
J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
443
Jaxi
J
C
  • Question
Dynamic calendar for parenting time
2
Replies
12
Views
362
CigarBuddha
C
E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
331
KRice
K

Forum statistics

Threads
1,214,651
Messages
6,120,744
Members
448,989
Latest member
mariah3

Share this page

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
Back
Top