• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formulas to calculate percentages

muna

Member
Please could someone address the comments in purple?

For the benefit of the search function here, I will summarise what I need help with...

A formula to calculate when a document was last saved. Return a result of the date, time and author.

A percentage to calculate how many queries were resolved over a period of time.
 

Attachments

  • Action Log with comments (1).xlsx
    17.3 KB · Views: 13
Here is a VBA solution for the last saved and Saver

Code:
Sub LastSaved()
    Dim x As Date
    x = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
    Range("F1") = x
    Range("E3") = ThisWorkbook.BuiltinDocumentProperties("Last Author")
    
End Sub
 
Here is a VBA solution for the last saved and Saver

Code:
Sub LastSaved()
    Dim x As Date
    x = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
    Range("F1") = x
    Range("E3") = ThisWorkbook.BuiltinDocumentProperties("Last Author")
   
End Sub
Thank you very much.

Is there a solution without VBA i.e. with formulas only?

Anyone else - Feel free to help with the other queries in the attached. Many thanks.
 
Only other way, is to go to File-->Info and read the data that is there. I am not aware of any formulas. You can create a UDF in VBA that replicates the data I provided, but that is still VBA.
 
HI,

For formula for percentage use something like this

=SUMPRODUCT(--($C$15:$C$22=1),--(MONTH(F15:F22)=3),--(H15:H22="Met"))/SUMPRODUCT(--(MONTH($F$15:$F$22)=3))

Note: the formula is hard coded with month number as 3.
Regards,
 
Here is a VBA solution for the last saved and Saver

Code:
Sub LastSaved()
    Dim x As Date
    x = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
    Range("F1") = x
    Range("E3") = ThisWorkbook.BuiltinDocumentProperties("Last Author")
   
End Sub

HI,

For formula for percentage use something like this

=SUMPRODUCT(--($C$15:$C$22=1),--(MONTH(F15:F22)=3),--(H15:H22="Met"))/SUMPRODUCT(--(MONTH($F$15:$F$22)=3))

Note: the formula is hard coded with month number as 3.
Regards,

Thanks, both. Really good progress.

I will shortly upload an updated spreadsheet with comments to help track progress.

Feel free to have a further look. I would appreciate it.
 
The above only updates when you click on 'save'. Is there a way to do it so that you don't need to click on the above 'Save' button for the author, date and time to update.

When do you want the data in those fields to appear. Upon opening the file? Upon double clicking in one of the two fields? Or some other action. Help us here. Also, the file you uploaded does not contain the current code that was supplied to you. With code you need to save the file as an .xlsb or .xlsm
 
When do you want the data in those fields to appear. Upon opening the file? Upon double clicking in one of the two fields? Or some other action. Help us here. Also, the file you uploaded does not contain the current code that was supplied to you. With code you need to save the file as an .xlsb or .xlsm

Sorry for the confusion.

This is no longer required but thank you anyway.
 
HI,

For formula for percentage use something like this

=SUMPRODUCT(--($C$15:$C$22=1),--(MONTH(F15:F22)=3),--(H15:H22="Met"))/SUMPRODUCT(--(MONTH($F$15:$F$22)=3))

Note: the formula is hard coded with month number as 3.
Regards,

Sorry to be a pain but could you please change this formula so it returns a number rather than a percentage? I also require the formula to say 0% if that's the answer rather than DIV%? Or something change like that.
 
Common Courtesy at these forums usually means you provide a response that is indicative of what happened.

This is no longer required but thank you anyway.
really leaves me with an empty feeling. Did it work? was it replaced with something else? Am I helping? Am I wasting my time trying to help you? Should I ignore any requests from you in the future?
 
Back
Top