Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Date Formula

Posted on 2013-01-20
22
Medium Priority
?
538 Views
Last Modified: 2013-01-24
I pull a year to date report for trouble tickets. What I wanted to do is if the date in cell A2 is between 2 certain dates suchs as Monday January 14th and Friday January 18th I want it to Dispaly January 18th (Fridays date) in Z2. The reason being is I want to pivot the week ending results. Unless someone has an easier way?
0
Comment
Question by:Edward Pamias
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +3
22 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1000 total points
ID: 38798855
Try

=A2+7-WEEKDAY(A2,16)
0
 
LVL 57

Assisted Solution

by:giltjr
giltjr earned 1000 total points
ID: 38798859
Assuming the date you want is in Column A:

=SUM(A1+(7-(WEEKDAY(A1,16))))
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38798861
if you pull a weekly report then why not do
WEEKNUM("1/14/2013") will return 3 and
WEEKNUM("1/18/2013") will also return 3
then no matter what the date is will return same value if in same week.
gowflow
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 18

Author Comment

by:Edward Pamias
ID: 38798864
My week is only 5 days. The techs do not work over the weekend.  =X2+(5-WEEKDAY(X2,2)) - <--- here is the formula that works for me. Thanks for the help guys!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38798868
Try

=A2+7-WEEKDAY(A2,16)
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 38798920
I've requested that this question be closed as follows:

Accepted answer: 0 points for epamias's comment #a38798864

for the following reason:

Thanks all for the suggestions!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 38798869
did yyou try mine ??? Weeknum !!!
gowflow
0
 
LVL 57

Expert Comment

by:giltjr
ID: 38798904
Did you come up with your formual before or after ssaqibh's post?

It is typically common courtesy to award some points to answers that helped you come to the your solution.  

Especially if they work, which ssaqibh's solution works independent of the number of days your staff works.  I know it works because if you look its the same as mine, and I tested my.

As for gowflow, unfortunately using the weeknum does not help if you need the date.  Unless you know something that you are not posting, you can't find the date of the last day of the week just knowing the week num, you would need to know some date during the week.  However if you notice the formula posted by ssaqubh (and myself)  do make use of weeknum.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38798921
In what way does your formula give different results from the proposed solution?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38798932
Note that WEEKDAY with 16 as second argument is only available in Excel 2010 or later versions - you can get the same results with these versions

=A2+7-WEEKDAY(A2+1)

and

=A2+7-WEEKDAY(A2,16)

The former works in any version of excel

regards, barry
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 38799079
After the first 2 posts I did come up with formula. The first 2 did not work for me. So I went and searched for another solution while I was waiting. I modified what I found to work for me. You can close this question.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38799086
Which excel are you using?
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 38799087
I tried the other formulas as well they did not work even after modifications.
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 38799091
Excel 2010 - my formula worked fine.  =X2+(5-WEEKDAY(X2,2))  I answered my own question. Since it is a 5 day work week this works. Thanks.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38799095
Can you show us how the results of the first formula differ from yours?
0
 
LVL 57

Expert Comment

by:giltjr
ID: 38799288
Um, I know I tested what I posted and it worked.  The only difference between mine and ssagibh's is I explicitly used the SUM function.  So I know his workes also.

In fact here are the results from all 3 formulas, hopefully it looks O.K. and makes sense.
 The only differnece game on the weekend.  Your's puts the date as the prior Fri., ours puts it as the following.

DOW        Actual Date      Yours      ssagibh's      Mine
MON      1/21/2013      1/25/2013      1/25/2013      1/25/2013
TUE      1/22/2013      1/25/2013      1/25/2013      1/25/2013
WED      1/23/2013      1/25/2013      1/25/2013      1/25/2013
THUR      1/24/2013      1/25/2013      1/25/2013      1/25/2013
FRI      1/25/2013      1/25/2013      1/25/2013      1/25/2013
SAT      1/26/2013      1/25/2013      2/1/2013      2/1/2013
SUN      1/27/2013      1/25/2013      2/1/2013      2/1/2013
MON      1/28/2013      2/1/2013      2/1/2013      2/1/2013
TUE      1/29/2013      2/1/2013      2/1/2013      2/1/2013
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 38799321
The formula I use  which works fine for me is in the attached sheet.. Keep in mind Friday of each week is the week ending date.   See attached sheet.
sampleBook1.xlsx
0
 
LVL 18

Author Comment

by:Edward Pamias
ID: 38799330
I just realized for desktop the beginning of my week is Monday and ends Friday. So the formula works fine but for Mobility this wont work since my week starts Saturday and ends Friday.
0
 
LVL 57

Expert Comment

by:giltjr
ID: 38799506
However the formula first posted by ssaqibh will put the date for Sat. as the following Fri., which seem to be what your software product needs.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 38800796
To throw another option into the fray:

=CEILING(X2,7)-1

This will round a date up to the following Friday. The CEILING function rounds up to factor specified, in this case 7 which ends up at the Saturday and then minus 1 to get Friday.

Likewise, FLOOR will round down.

This works because Day 1 in Excel history, back in January 1900, is a Sunday.

Thanks
Rob H
0
 
LVL 18

Author Closing Comment

by:Edward Pamias
ID: 38800985
Thanks guys.... they first 2 did worked for me.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38814761
>=CEILING(X2,7)-1

Hello Rob,

I always prefer to use WEEKDAY function because results will be consistent regardless of date system - if you are using 1904 date system then the above formula will give you a Thursday date rather than a Friday

regards, barry
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question