Formula To Find A "week Ending" Sunday Date 

Formula To Find A "week Ending" Sunday Date  Excel 
View Answers 
I need help with a formula. I have a certain list of dates, by day...and I need them listed by "week ending" (where a week "ends" on a Sunday).
For example, for the week ending 5/23, I might have the dates:
5/23/10 (Sunday)  the formula should pull 5/23/10
5/22/10 (Saturday)  the formula should pull 5/23/10
And if the date was earlier...say 5/11/10...it should return "5/16/10" because that is the Sunday of the week that contained 5/11/10.
I know there is a way to do this with the Weekdays function combined in a formula, but I don't remember what it is. Does anyone know?
Thanks!
SundayST
For example, for the week ending 5/23, I might have the dates:
5/23/10 (Sunday)  the formula should pull 5/23/10
5/22/10 (Saturday)  the formula should pull 5/23/10
And if the date was earlier...say 5/11/10...it should return "5/16/10" because that is the Sunday of the week that contained 5/11/10.
I know there is a way to do this with the Weekdays function combined in a formula, but I don't remember what it is. Does anyone know?
Thanks!
SundayST
Free Excel Courses
Similar Excel Tutorials
Use a function in Excel to get the number of the day in a week, from 1 to 7. This allows you to use your dates to ...
How to get the last day of the month, including the date and day of week, for any date in Excel. This method allow ...
How to get the name of a day from a date in Excel. This returns, for example, "Tuesday" for the date October 4, 201 ...
Use a formula or formatting to display the current day of the week in Excel. Sections: Formula to Show Current Day ...
Subscribe for Weekly Tutorials
Helpful tutorials delivered to your email!
Helpful Excel Macros
 Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Similar Topics
I need a formula to calculate Turn Around Time. Find below the sample data and details for your reference.
Received Date & Time: 10/29/2010 3:27:00 PM
Completed Date & Time: 11/1/2010 3:57:32 AM
Business Hours: 08:00 AM to 05:00 PM
Non Business hours: 05:00 PM to 08:00 AM
Weekdays : Monday to Friday
Weekends: Saturday & Sunday
If I calculate TAT for this time period it should show me 1 hour 33 minutes, why because business hours are closing by 5:00 PM on 10/29 so it should not calculate duration after 5:00 PM to until 08:00 AM on next day, however 10/29 is Friday so formula should calculate the duration from 10/29 Friday 3:37 PM to 10/29 5:00 PM and again it should calculate from 8:00 AM on 11/01 Monday and formula should skip Saturday & Sunday.
In this scenario I have completed the task before 8:00 AM on 11/01 so it should show duration of 1hour 33 minutes.
Please let me know if you need any more details
Received Date & Time: 10/29/2010 3:27:00 PM
Completed Date & Time: 11/1/2010 3:57:32 AM
Business Hours: 08:00 AM to 05:00 PM
Non Business hours: 05:00 PM to 08:00 AM
Weekdays : Monday to Friday
Weekends: Saturday & Sunday
If I calculate TAT for this time period it should show me 1 hour 33 minutes, why because business hours are closing by 5:00 PM on 10/29 so it should not calculate duration after 5:00 PM to until 08:00 AM on next day, however 10/29 is Friday so formula should calculate the duration from 10/29 Friday 3:37 PM to 10/29 5:00 PM and again it should calculate from 8:00 AM on 11/01 Monday and formula should skip Saturday & Sunday.
In this scenario I have completed the task before 8:00 AM on 11/01 so it should show duration of 1hour 33 minutes.
Please let me know if you need any more details
Hello,
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKEDOR choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKEDOR choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
I'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.
The days of the week consists of "Name" "Date" Hours"
The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"
Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.
I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.
I have attached a sample file.
Thanks in advance for your help.
I am trying to correct a formula for a productivity worksheet in excel. The idea is that we would have the ability to input the number of hours worked each day and the number of direct hours (working with clients) per day. Then take the daily percentages and add them then devide them to determine the final productivity percentage for the week. Additionally not everyone works a full week so I would need it to only divide the percentages of the days that were completed. Any suggestions? or does anybody know a formula that would accomplish this already? I am good with basic excel and was able to identify a flaw in the current productivity sheet.
I need a formula to calculate overtime, after a work week of 40 hours.
for example: if an employee works 10 hours a day we would not count towards overtime until the employee completes a 40 hour work week.
Any suggestions would be greatly appreciated!!!!!
Thanks,
YV
for example: if an employee works 10 hours a day we would not count towards overtime until the employee completes a 40 hour work week.
Any suggestions would be greatly appreciated!!!!!
Thanks,
YV
Peace to you all
I'm still new here but after I searched the forum I found great work done
for the needy like me.
So I'm so hopeful
I'm concerned with making a timetable for my school which we used to do it manually
I need a code to distribute each teacher timetable with the following basis:
the number of classes_ the teacher is assinged to work with_multiplied by the number of periods ( which is changeable ).
e.g ; Teacher 1 has 2 classes (11 &12). Each class should be visited 9 timea a week.
So Teacher 1 should work 18 periods a week
the distribution I need 'd look like the example in Sheet("timetable")
Please don't let me down
any help 'd be greately appreciated
I'm still new here but after I searched the forum I found great work done
for the needy like me.
So I'm so hopeful
I'm concerned with making a timetable for my school which we used to do it manually
I need a code to distribute each teacher timetable with the following basis:
the number of classes_ the teacher is assinged to work with_multiplied by the number of periods ( which is changeable ).
e.g ; Teacher 1 has 2 classes (11 &12). Each class should be visited 9 timea a week.
So Teacher 1 should work 18 periods a week
the distribution I need 'd look like the example in Sheet("timetable")
Please don't let me down
any help 'd be greately appreciated
I'm trying to figure out what the formula is to multiply the total hours worked per week by their hourly pay for each individual. kind of like the
=Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it.
Thanks!
charitydc
=Sum(I25:I32) Formula but I want to multiply the outcome of that formula by the hourly rate of 11.00, 15.00 and 16.00. Does anybody know how this can be done? If so I would greatly appreciate it.
Thanks!
charitydc
Hey everyone,
I'm really hoping someone can help me with this...
I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?
I'm really hoping someone can help me with this...
I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?
I'm looking for a formula that pulls the text from a cell unti it hits a space.
I'm using the formula below but keep getting #VALUE results
B1: =LEFT(A1,FIND(",",A1,1))
I know it's not that hard but can't figure it out.
thanks for the help
max
I'm using the formula below but keep getting #VALUE results
B1: =LEFT(A1,FIND(",",A1,1))
I know it's not that hard but can't figure it out.
thanks for the help
max
I am using the following formula to calculate the number of days between a date and the current date: =DATEDIF(A1,TODAY(),"d") What do I add to the formula to leave cell blank if no date is entered in the cell for calculation.
From limited experience I know that excel calculates dates via serial numbers.
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
Ok, I'm going to try and explain this. I am going to have 5 data points in cells such as A2:A6
All I need is to know if there is an upward trend or downward trend of the numbers.... I can't have something that simply takes the first and last number and checks if it's higher or lower. I need to actually see if they are trending upwards. Also how would i go about making it so that if they are trending upwards it shows an up arrow, and down arrow for downwards...
And finally, if the trend continues at the same rate, how long it will take the number to reach a goal. Each data point will be 1 week apart, with an ultimate goal needed to be achieved at the end.
This will be a completely separate formula on the sheet....
Any help?
All I need is to know if there is an upward trend or downward trend of the numbers.... I can't have something that simply takes the first and last number and checks if it's higher or lower. I need to actually see if they are trending upwards. Also how would i go about making it so that if they are trending upwards it shows an up arrow, and down arrow for downwards...
And finally, if the trend continues at the same rate, how long it will take the number to reach a goal. Each data point will be 1 week apart, with an ultimate goal needed to be achieved at the end.
This will be a completely separate formula on the sheet....
Any help?
I'm trying to format a worksheet to show how many days overdue certain tasks are, and running into a couple of issues. The sheet is set up with Due Dates in Column A, Days Overdue in Column B, and Date Completed in Column C. My first thought was just to use as simple a formula as possible in Column B =Today()A2. This worked alright for tasks that already had Due Dates assigned, but if Column A was blank I'd get a high number in Column B. I also realized that tasks will continue to show as overdue even after I enter the completedon date in Column C. Is there a relatively simple way to tackle this formula? Thanks in advance for suggestions. (I've attached an example with the column headings I want to use.)
Need a formula to calculate weeks stock in hand based on 12 months forecast.
Here is the example.
Jan2011 Feb2011 Mar2011 Apr2011 May2011 Jun2011 Jul2011 Aug2011 Sep2011 Oct2011 Nov2011 Dec2011             Product XXXXX Actual/Forecast : 174 1674 574 574 524 524 524 524 524 728 728 728 New Purchases : 0 0 0 0 1002 0 0 0 0 0 0 0 Goods In Transit: 0 1506 0 0 0 0 0 0 0 0 0 0 Other Movements : 0 0 0 0 0 0 0 0 0 0 0 0 Closing Stock : 1270 1102 528 46 432 92 616 1140 1664 2392 3120 3848 Week Stock in Hand ? ? ? ? ? ? ? ? ? ? ? ?
So, in Month of Jan2011 the Closing stock is 1270, need a formula to calculate how many weeks will this stock cover look at future forecast.
Here is the example.
Jan2011 Feb2011 Mar2011 Apr2011 May2011 Jun2011 Jul2011 Aug2011 Sep2011 Oct2011 Nov2011 Dec2011             Product XXXXX Actual/Forecast : 174 1674 574 574 524 524 524 524 524 728 728 728 New Purchases : 0 0 0 0 1002 0 0 0 0 0 0 0 Goods In Transit: 0 1506 0 0 0 0 0 0 0 0 0 0 Other Movements : 0 0 0 0 0 0 0 0 0 0 0 0 Closing Stock : 1270 1102 528 46 432 92 616 1140 1664 2392 3120 3848 Week Stock in Hand ? ? ? ? ? ? ? ? ? ? ? ?
So, in Month of Jan2011 the Closing stock is 1270, need a formula to calculate how many weeks will this stock cover look at future forecast.
Hi there
I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:
Column M  Estimated Delivery dates
Column N  Actual Delivery dates
Column O  =IF(SUM(M2N2)>0,1,0)
Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))
This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?
Any help much appreciated
Thanks,
James
I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:
Column M  Estimated Delivery dates
Column N  Actual Delivery dates
Column O  =IF(SUM(M2N2)>0,1,0)
Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))
This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?
Any help much appreciated
Thanks,
James
Since there isn't a ISDATE function in excel.
The following can be used to simulate the same, and avoid having to achieve the same with VBA's IsDate function:
STEPS:
1 Format the column (ex A) as text
2 Formula to check for valid dates:
=ISERROR(DATEVALUE(A1))
The following can be used to simulate the same, and avoid having to achieve the same with VBA's IsDate function:
STEPS:
1 Format the column (ex A) as text
2 Formula to check for valid dates:
=ISERROR(DATEVALUE(A1))
I often use if statements to return empty cells, for example:
=IF(a1=0,"ERROR","")
The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrowdown because it seems to think that these empty cells have contents.
Is there a way to designate, in such a formula as above, to return a TRULY empty cell?
Thanks
=IF(a1=0,"ERROR","")
The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrowdown because it seems to think that these empty cells have contents.
Is there a way to designate, in such a formula as above, to return a TRULY empty cell?
Thanks
A previous poster explained what I also need; there were no responses,
so I thought I would try again.
Does anyone know of an Excel templateor a small standalone
programthat will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or lowcost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.
Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."
Thanks very much.
so I thought I would try again.
Does anyone know of an Excel templateor a small standalone
programthat will calculate the interest/principal breakdown when
payments are varied in amount and frequency? Free or lowcost, please.
I need one that will work on my Mac/Office 2004. I will need to print
out periodic reports.
Here is the way the previous poster described it:
"Excel template: Loan Amortization for random/irregular payments,
figures days
between payment dates.
I have a loan with a variable beginning balance and irregular payments
with
annual large payment. (based on collections)
Would like to enter payment and date.
then Excel would figure days since last payment, interest amount,
principal
amount, Ending Principal balance.
And total interest paid, total pricipal paid
If I change the starting principal, excel would recalculate all
entries."
Thanks very much.
I have two columns in my spreadsheet. Column A has no blank fields, Column B has some blank fields. I would like to show the text from Column A in the same row of Column B only when Column B is blank.
So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.
Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?
So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.
Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?
Hi, hope someone can assist, i want to add some arrow syimbols in to a formula, so that when the formula works out it shows an up arrow for higher or a down arrow for lower, i am using this formula at the moment because i dont know any better but it works, putting in the word up or down:
I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?
Thanks
I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?
Thanks
hi,
I would like a formula that would list all the items in row B that match the criteria in row A. the first cell with formula would list the first item, the second cell with the formula would list the next item, and so forth. Also, column B might have a duplicates that should be listed. Is this possible? I cannot manipulate the order of the original items (ie, filters) because this data is being used to derive other formulas.
I would like a formula that would list all the items in row B that match the criteria in row A. the first cell with formula would list the first item, the second cell with the formula would list the next item, and so forth. Also, column B might have a duplicates that should be listed. Is this possible? I cannot manipulate the order of the original items (ie, filters) because this data is being used to derive other formulas.
Hi,
Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.
Please HELP!!!
Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.
Please HELP!!!
In excel, I am trying to find a formula or funtion so that when I enter a
date (1/10/05) in cell A1 for example, then B1 automatically changes to
1/11/05, C1 to 1/12/05, etc. Can anyone help? Thanks!!
date (1/10/05) in cell A1 for example, then B1 automatically changes to
1/11/05, C1 to 1/12/05, etc. Can anyone help? Thanks!!
Hi all,
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin
I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!
I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.
I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)
Any help you can give would be much appreciated.
Cheers,
Kevin
Hi was wondering if anyone can help.
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
Any help would be very much appreciated
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
Any help would be very much appreciated