date computation

[jv]

Good day to all,

Can you help me make a formula for the following:

1 - from a given date (colum e) will be added 10 days
in column f, but if the result will fall on thursday
or friday it should be moved to the next day which is
saturday.

2 - typing a date in column b will give the corresponding
day in column c.
ex. column: B C
03-Feb-04 Tuesday

Thanks with anticipation.

Regards

jv

 

[Frank Kabel]

Hi Jv
1. One way:
=IF(OR(WEEKDAY(A1,1)=2,WEEKDAY(A1,1)=3),A1+14-WEEKDAY(A1,1);A1+10)
2. one way: enter the formula =B1 and format the resulting cell with
the custom format "dddd"
another way: =TEXT(B1,"dddd")

HTH
Frank

 

[Bob Phillips]

Good Day JV,

Here's the straight way

=IF(WEEKDAY(A1+10)=5,A1+12,IF(WEEKDAY(A1+10)=6,A1+11,A1+10))

Here's a slightly more ambiguous alternative with no IFs

=A1+10+(WEEKDAY(A1+10)=5)*2+(WEEKDAY(A1+10)=6)*1

The second part is easily achieved with

=TEXT(B1,"dddd")

which has to be setup in advance or copied down, or worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 2 Then
Target.Offset(0, 1).Value = Format(Target.Value, "dddd")
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[jv]

Frank,

I got an error message from your formula, is there
something i missed in applying your tip?

jv

 

[jv]

Bob,

The formula works great but when it encounters a blank
cell it still giving a date instead of a blank or "-"
result.

Thanks

jv

 

[Frank Kabel]

Hi Jv
just a small typo (due to the translation). Try
=IF(OR(WEEKDAY(A1,1)=2,WEEKDAY(A1,1)=3),A1+14-WEEKDAY (A1,1),A1+10)

HTH
Frank

 

[Frank Kabel]

Just saw your ofther question. If you want to check for blanks cells
use something like:
=IF(A1<>"",IF(OR(WEEKDAY(A1,1)=2,WEEKDAY(A1,1)=3),A1+14-WEEKDAY
(A1,1),A1+10),"---")
Frank

 

[jv]

Frank,

Everything is ok now and the spreadsheet is working
perfectly.

There's one more favor, we have added another column,
where a date of transaction should be inputted for
scheduling, but if the date falls on 'thursday'
or 'friday', a text message on the other column will
appear.

ex. column A B
02.02.04 No transaction on this date


Is that possible?


Thank you so much.

jv

 

[Frank Kabel]

Hi jv
enter the following in B1:
=IF(A1<>"",IF(OR(WEEKDAY(A1,1)=5,WEEKDAY(A1,1)=6),"No transaction on
this date",""),"")

HTH

 

[Bob Phillips]

JV,

Enhancements

=IF(A1="","",IF(WEEKDAY(A1+10)=5,A1+12,IF(WEEKDAY(A1+10)=6,A1+11,A1+10)))

=IF(A1="","",(A1+10+(WEEKDAY(A1+10)=5)*2+(WEEKDAY(A1+10)=6)*<>"")

it will still show 1st Jan 1900 if the cell is formatted as date

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[jv]

Frank,

Once again, thank you so much!

Our work are much easier now compare before where we need
to triple check the date entry.

regards,

jv

 

[jv]

Bob,

The formula works great!

Thank you.

Regards,

jv

 

[Frank Kabel]

Hi jv
glad to be of some help
Frank