如果日期在周末默认为上周五，则为excel [英] excel if date falls on weekend default to previous friday

问题描述

我在不同的列中具有以下两个功能，并且我需要对两个都应用一个功能:

I have the following two functions in different columns and I need to apply a function to both :

=DATE(YEAR(E5);MONTH(E5);DAY(E5))

=TRUNC([@CAT]-TIME(10;0;0))

对于两个人，如果日期都在周末，我需要默认为星期五.如果日期日期在一周中，则需要保留工作日日期.

For both of them I need to default to a Friday if the date falls over the weekend. if date date is during the week it needs to keep the weekday date.

例如:2018/03/11星期日必须为2018/03/09

e.g: Sunday 2018/03/11 needs to be 2018/03/09

例如:2018/03/5星期一需要保持不变2018/03/05

e.g: Monday 2018/03/5 needs to stay the same 2018/03/05

我尝试将IF语句与工作日(1-7)一起使用，但是如果它为假，则从日期开始减去一两天(因此在星期四，它转到星期二)

I have tried using an IF statement with a Weekday (1-7) but when its false it minus a day or two from the date (So on a Thursday it goes to Tuesday)

推荐答案

=WORKDAY(your_formula +1;-1)

会做你想要的.

如果your_formula解析为周六或周日，并且我们加一(1)天，然后减去一(1)个工作日，则前一个星期五将是结果，因为周日或周一减去一个工作日->星期五.

If your_formula resolves to a Sat or Sun, and we add one(1) day, then subtract one (1) workday, the preceding Fri will be the result, Since either Sun or Mon minus one workday --> Friday.

这篇关于如果日期在周末默认为上周五，则为excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！