Forum Discussion
Solved
Help with an excel formula involving days of the week
Hello,
The Problem
I am looking at ways to save time at my job and one of the ways I came up with was using formulas in my spreadsheets instead of inputting all the data manually.
The Resolution?
For the purpose of this example, weeks start on Sunday and end on Saturday.
I want to be able to enter a date, and then have two cells below stating what the Sunday of that week is, and what the Saturday of that week is.
If I enter a Sunday, then obviously the Sunday date would be the same.
I hope this makes sense, and if it does, is there a formula for this?
Kind regards,
Tomjohn93
tomjohn93 -Please check this !
=IF(TEXT(A1,"dddd")="Sunday",A1,A1-(WEEKDAY(A1)-1)) =IF(TEXT(A1,"dddd")="Saturday",A1,A1+(7-WEEKDAY(A1)))
6 Replies
tomjohn93 -Please check this !
=IF(TEXT(A1,"dddd")="Sunday",A1,A1-(WEEKDAY(A1)-1)) =IF(TEXT(A1,"dddd")="Saturday",A1,A1+(7-WEEKDAY(A1)))- SanthoshKunder
Thank you that works great, thanks for your help
revised
- Hi Patrick,
Thanks for your response, however I tried it and it only works when the day you enter is a Sunday as it outputs back the next Sunday and Saturday,, however if, for example I entered the 11th October (Wednesday) I would want to then get back the 8th October (Sunday) and 14th October (Saturday) but Instead I get back 14th October and then the 15th October which is the next Sunday.- I see. I don't believe you made that clear in your post but I'm glad you have a solution.
