Ramesh Deo
Member
Hi All,
plz solve a little bit query if any one free coz 2day is sunday!!!!
plz solve a little bit query if any one free coz 2day is sunday!!!!
solve this query....
- Thread starter Ramesh Deo
- Start date
NARAYANK991
Excel Ninja
Hi Ramesh ,
Given the fact that on a Sunday no wants to spend time , you have also not spent time on your question !
Firstly , there are 3 questions , all of them unrelated to one another.
Second , the questions do not provide complete information.
Lastly , it is not even clear what you want.
1. What do you want to do in question 1 ? I am reproducing the question here , in case anyone wants to interpret and explain the question without having to download the file :
How can remove blanks for only rows not blank cells if we have multiple data.
2. What is the requirement in question 2 ? What is the date in L2 for ? Do you want the question answered for the month of May ? If so , the question is reproduced here , again for interpretation :
count second saturday and last saturday in a month.
3. What does question 3 mean ? You cannot subtract a greater time from a lesser time. Here too , the question is reproduced :
subtraction of time we have multiple data
Please take the time and trouble to explain each of your questions in detail.
Narayan
Given the fact that on a Sunday no wants to spend time , you have also not spent time on your question !
Firstly , there are 3 questions , all of them unrelated to one another.
Second , the questions do not provide complete information.
Lastly , it is not even clear what you want.
1. What do you want to do in question 1 ? I am reproducing the question here , in case anyone wants to interpret and explain the question without having to download the file :
How can remove blanks for only rows not blank cells if we have multiple data.
2. What is the requirement in question 2 ? What is the date in L2 for ? Do you want the question answered for the month of May ? If so , the question is reproduced here , again for interpretation :
count second saturday and last saturday in a month.
3. What does question 3 mean ? You cannot subtract a greater time from a lesser time. Here too , the question is reproduced :
subtraction of time we have multiple data
Please take the time and trouble to explain each of your questions in detail.
Narayan
Ramesh Deo
Member
Ramesh Deo
Member
Very simple question for u all sir,bt i think there is problem in explanation about question.
anyway again I am trying to explain the question.
In
Q.1- How can we remove only blanks rows, not a blank cell as in given example in uploaded sheet.
Q.2-How to count a days for example saturday in a month.
Q.3-how can we subtract for given time in example.
this was my question, i hope u got it, if didn,t, then hw can u solve this queries.
thanks 4 given your precious time.
anyway again I am trying to explain the question.
In
Q.1- How can we remove only blanks rows, not a blank cell as in given example in uploaded sheet.
Q.2-How to count a days for example saturday in a month.
Q.3-how can we subtract for given time in example.
this was my question, i hope u got it, if didn,t, then hw can u solve this queries.
thanks 4 given your precious time.
Somendra Misra
Excel Ninja
Hi Ramesh Deo,
To your question number 2 you can use below formula to count no. of Saturday in a month:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(L2&":"&EDATE(L2,1)-1)),2)=6))
Here month starting date is in L2. And 6 shown in red in formula is for Saturday. So, 1 - 7 is for Monday to Sunday.
To your Q3. If you want to find number of hours between start time & end time use below formula construct:
=MOD(end time-start time,1)*24
To your Q1. I think VBA can give you a solution, if it is OK with you.
Regards,
To your question number 2 you can use below formula to count no. of Saturday in a month:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(L2&":"&EDATE(L2,1)-1)),2)=6))
Here month starting date is in L2. And 6 shown in red in formula is for Saturday. So, 1 - 7 is for Monday to Sunday.
To your Q3. If you want to find number of hours between start time & end time use below formula construct:
=MOD(end time-start time,1)*24
To your Q1. I think VBA can give you a solution, if it is OK with you.
Regards,
Ramesh Deo
Member
Hi SM Sir,
How r u?
I know, u have the answer for my question.
Sir, I tried this formula bt i didn't get the result.for question no 2 result comes 0:00 and for question no 2 result comes 12:00 AM,bt difference will be 1 hour.
for Q1 any shortcut if no then do through VBA
so plz do once i my uploaded sheet.plz
thanks & regards
Deo
Last edited by a moderator:
Somendra Misra
Excel Ninja
@Ramesh Deo
I am fine and hope the same for you.
Please note I could answer your query only after valid questions raise from Narayan Sir.
Format the cell to General for question no. 2.
For question no. 3 also format the cell to general.
For Q1. I think you should wait for Narayan Sir, may be he will have a shortcut other than VBA.
Regards,
I am fine and hope the same for you.
Please note I could answer your query only after valid questions raise from Narayan Sir.
Format the cell to General for question no. 2.
For question no. 3 also format the cell to general.
For Q1. I think you should wait for Narayan Sir, may be he will have a shortcut other than VBA.
Regards,
Ramesh Deo
Member
ok thanks SM Sir
Sir,One thing more I have a little bit confusion about pivot chart.I made a pivot chart bt its not being change when range of data change through vlookup formula,bt i have seen when range of data change then automaticlly pivot chart changed.so plz let me asap is any option to link with or smthing else...
Ramesh Deo
Member
if u want a sample sheet then i can upload a sample file.