Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show dates based on criteria
In Row T6 I enter the first day of the pay week (Sunday) in the form 5/4/2008 it appears as May 4 2008 Then In row D13 the last day of the pay week(Saturday) then appears as May 10 2008 In this cell I have the formula =IF(T6=**,**,IF(WEEKDAY(T6)=7,T6,IF(WEEKDAY(T6)=6, T6+1,IF(WEEKDAY(T6)=5,T6+2,IF(WEEKDAY(T6)=4,T6+3,I F(WEEKDAY(T6)=3,T6+4,IF(WEEKDAY(T6)=2,T6+5,T6+6))) ))))) Currently G8 Says Monday G9 Tuesday, G10 Wednesday, G11 Thursday, G12 Friday I would now like Mondays row to give the date Monday May 5 2008, Tuesday May 6 2008 and so on... all based on the original date entered in T6 Is This possible? thanks Sean -- slaga |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show dates based on criteria
It's not real clear what you're trying to do.
However: =IF(T6=**,**,IF(WEEKDAY(T6)=7,T6,IF(WEEKDAY(T6)=6, T6+1,IF(WEEKDAY(T6)=5,T6+2,IF(WEEKDAY(T6)=4,T6+3,I F(WEEKDAY(T6)=3,T6+4,IF(WEEKDAY(T6)=2,T6+5,T6+6))) ))))) You can replace that formula with this one: =IF(T6="**","**",T6+CHOOSE(WEEKDAY(T6),6,5,4,3,2,1 ,0)) Not sure what your intentions are with the =IF(T6=**,**, part but you need to enclose those ** in quotes. -- Biff Microsoft Excel MVP "slaga" wrote in message ... In Row T6 I enter the first day of the pay week (Sunday) in the form 5/4/2008 it appears as May 4 2008 Then In row D13 the last day of the pay week(Saturday) then appears as May 10 2008 In this cell I have the formula =IF(T6=**,**,IF(WEEKDAY(T6)=7,T6,IF(WEEKDAY(T6)=6, T6+1,IF(WEEKDAY(T6)=5,T6+2,IF(WEEKDAY(T6)=4,T6+3,I F(WEEKDAY(T6)=3,T6+4,IF(WEEKDAY(T6)=2,T6+5,T6+6))) ))))) Currently G8 Says Monday G9 Tuesday, G10 Wednesday, G11 Thursday, G12 Friday I would now like Mondays row to give the date Monday May 5 2008, Tuesday May 6 2008 and so on... all based on the original date entered in T6 Is This possible? thanks Sean -- slaga |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show dates based on criteria
I am embarassed to say that I hadent had my glasses on and that what I had thought were * 's...were " 's.... clearly was getting towards the end of the work shift lol thanks very much for your help, this new formula is far more concise, it helps alot. What I would like to do is extend this to acheive my goal. I will try to better explain what I am trying to acomplish. I am making changes to our companys time sheets. The User enters into T6 the date in D13 I now have your formula which shows the date for the pay weeks end =IF(T6="**","**",T6+CHOOSE(WEEKDAY(T6),6,5,4,3,2,1 ,0)) which based on t6 currently reads Saturday May 10 2008 Now Each work day the worker must enter the job number they worked on, and the amount of hours they worked at each job. so row G is the headings for the days of the week (Monday in G8, Tuesday in G9...Friday in G12) What I would like to do with these headings is such that when the user enters the date in T6, g8 will ready "Monday May 5th 2008" Tuesday May 6th 2008" and so on. I am assuming I will just need to make a change of some sort to the formula you provided me with last time. Thanks again for the help, Sean -- slaga |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show dates based on criteria
what I had thought were * 's...were " 's....
So, you were probably thinking this: If T6 is blank, return blank... =IF(T6="","",T6+CHOOSE(WEEKDAY(T6),6,5,4,3,2,1,0)) Ok, we can probably figure out how to get those dates posted but I need a better understanding. The user enters a date in T6 and D13 *always* calculates the Saturday date of the week based on T6. In G8:G12 you want the dates for Monday thru Friday of the *same* week as the date in D13? So, lets assume D13 = 9/13/2008 (Saturday). Are these the dates that should be posted to G8:G12 - G8 = 9/8/25008 (Monday) G9 = 9/9/2008 (Tuesday) G10 = 9/10/2008 (Wednesday) G11 = 9/11/2008 (Thursday) G12 = 9/12/2008 (Friday) If those are the correct dates enter this formula in G8 and copy down to G12: =IF(D$13="","",D$13-WEEKDAY(D$13,2)+ROWS(G$8:G8)) Format in DATE style of your choice. -- Biff Microsoft Excel MVP "slaga" wrote in message ... I am embarassed to say that I hadent had my glasses on and that what I had thought were * 's...were " 's.... clearly was getting towards the end of the work shift lol thanks very much for your help, this new formula is far more concise, it helps alot. What I would like to do is extend this to acheive my goal. I will try to better explain what I am trying to acomplish. I am making changes to our companys time sheets. The User enters into T6 the date in D13 I now have your formula which shows the date for the pay weeks end =IF(T6="**","**",T6+CHOOSE(WEEKDAY(T6),6,5,4,3,2,1 ,0)) which based on t6 currently reads Saturday May 10 2008 Now Each work day the worker must enter the job number they worked on, and the amount of hours they worked at each job. so row G is the headings for the days of the week (Monday in G8, Tuesday in G9...Friday in G12) What I would like to do with these headings is such that when the user enters the date in T6, g8 will ready "Monday May 5th 2008" Tuesday May 6th 2008" and so on. I am assuming I will just need to make a change of some sort to the formula you provided me with last time. Thanks again for the help, Sean -- slaga |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting dates based on certain criteria | Excel Worksheet Functions | |||
Counting unique dates based on selected criteria in a list | Excel Worksheet Functions | |||
How do I set up a graph to show a number of different criteria? | Excel Discussion (Misc queries) | |||
Criteria Lookup based on Dates | Excel Discussion (Misc queries) | |||
Show top five records based on meeting multiple criteria | Excel Worksheet Functions |