Date Formula Question_Follow-up for dates falling on same weekly cut-off

[NazieO]

Hi,

I want to assign a follow-up date the following week for dates that fall on the same week cutoff (start of week is Friday).
I'm trying to create an automatic pay date formula for transactions that fall under a certain cutoff. Here's an example
Column A - includes transaction dates
Column B - to return expected pay date based on the formula calculating the value in Column A

Scenario:
Any transactions between Friday the previous week to Thursday 11:59PM of the current week are scheduled to get paid on Thursday next week. So the cutoff really is Thursday before midnight (Thursday 11:59pm) of the current week for it to get paid the following Thursday.

Examples:
Nov 21, 2024 (Thu, until 11:59pm) transaction gets paid Nov 28, 2024
Nov 22, 2024 (Fri) transaction gets paid Dec 5, 2024
Nov 26, 2024 (Tue) transaction gets paid Dec 5, 2024
Nov 28, 2024 (Thu 11:59pm) transaction gets paid Dec 5, 2024
Nov 29, 2024 (Fri) transaction gets paid Dec 12, 2024

Thank you so much, will appreciate any response and guidance.

 

[AliGW]

Please provide a desensitised sample workbook. Which version of Excel is this for?

 

[p45cal]

Try something along the lines of:
=CEILING.MATH(A1-6,7)+12
where cell A1 contains the transaction date.
(If you don't have the CEILING.MATH function, just CEILING will give you the same result.

 

[NazieO]

Please provide a desensitised sample workbook. Which version of Excel is this for?

Hi Ali, using Professional MS office 2021. I actually have also been able to figure out a very simple formula. I got what I needed with the below formula:
=A2+(14-WEEKDAY(A2,15))

Transaction Date	Pay Date
21-Nov	28-Nov	Nov 21, 2024 (Thu, until 11:59pm) transaction gets paid Nov 28, 2024
22-Nov	5-Dec	Nov 22, 2024 (Fri) transaction gets paid Dec 5, 2024
26-Nov	5-Dec	Nov 26, 2024 (Tue) transaction gets paid Dec 5, 2024
28-Nov	5-Dec	Nov 28, 2024 (Thu 11:59pm) transaction gets paid Dec 5, 2024
29-Nov	12-Dec	Nov 29, 2024 (Fri) transaction gets paid Dec 12, 2024

Thank you so much for giving attention to the question.

 

[NazieO]

Try something along the lines of:
=CEILING.MATH(A1-6,7)+12
where cell A1 contains the transaction date.
(If you don't have the CEILING.MATH function, just CEILING will give you the same result.

View attachment 89270

Hi p45cal - I also tried this but showed different results for me...

Transaction Date	Paid Date (Weekday Formula)	CEILING.MATH Formula
21-Nov	28-Nov	28-Nov	Nov 21, 2024 (Thu, until 11:59pm) transaction gets paid Nov 28, 2024
22-Nov	5-Dec	28-Nov	Nov 22, 2024 (Fri) transaction gets paid Dec 5, 2024
26-Nov	5-Dec	5-Dec	Nov 26, 2024 (Tue) transaction gets paid Dec 5, 2024
28-Nov	5-Dec	5-Dec	Nov 28, 2024 (Thu 11:59pm) transaction gets paid Dec 5, 2024
29-Nov	12-Dec	5-Dec	Nov 29, 2024 (Fri) transaction gets paid Dec 12, 2024

 

[pecoflyer]

I have reported this member as I find the member's name is unacceptable

 

[AliGW]

NAZI is only a part of the user name. It is quite possible that this member has a real name that begins with Naz and is habitually shortened to Nazie. Furthermore, the 'z' may not be pronounced 'ts', but as an Anglicised 'z'. I think your reaction is most probably over-sensitive.

 

[pecoflyer]

NAZI is only a part of the user name. It is quite possible that this member has a real name that begins with Naz and is habitually shortened to Nazie. Furthermore, the 'z' may not be pronounced 'ts', but as an Anglicised 'z'. I think your reaction is most probably over-sensitive.

And I think you are probably far too lenient. It so happens that in German the "z" is also pronounces "ts"... That is how it all happened way back then. Pfff, quelle époque...

 

[AliGW]

I don't think you really understood my point. I am very well aware of the German 'z' (you know very well that I am fluent in German). I can see nothing offensive here, intended or otherwise. I am pretty certain that this is the member's real name. My reaction has nothing to do with leniency or 1930s Germany, and nor does this member's user name.

 

[Luke M]

As this forum is open internationally, it is unfair to assume pronunciation or spelling of user names. OP's user name is perfectly valid in some regions of the world.

 

[NazieO]

Hello all, apologies if I may have offended some with my username. I have not even thought of NAZI or Nazis. I go by the name Nazie (Natzie but the T is usually omitted by people when I pronounce my name so I adapted Nazie and my lastname's initial is O, hence the username NazieO. Do I need to change it (which I am willing to do)?

 

[Marc L]

Hello, no according to post #10, anyway as per your will …​

​

 

[NazieO]

I don't think you really understood my point. I am very well aware of the German 'z' (you know very well that I am fluent in German). I can see nothing offensive here, intended or otherwise. I am pretty certain that this is the member's real name. My reaction has nothing to do with leniency or 1930s Germany, and nor does this member's user name.

Appreciate you. No intent here to offend anyone. Thank you....