date computation

  • Thread starter Thread starter jv
  • Start date Start date
J

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
 
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
 
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)
 
Frank,

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

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