+ Reply to Thread
Results 1 to 8 of 8

Calculate total number of hours in current month

  1. 05-07-2023, 11:37 AM #1
    Shalaby
    Shalaby is offline
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Exclamation Calculate total number of hours in current month

    Originally I want to calculate the percentage between a cell contains hours formatted as hh:mm to the total number of hours of the current month. The current month is a name defined in the name manager is a text, ex:April.
    So if the current month is April so the number of hours is 30days*24hr=720 hr. Then the formula will calculate the percentage of the cell ex 112:23 (112 hr and 23mins) to the 720 hr > 112:23 / 720 and gives the result in %
    I tried the following formula to calculate the number of days in April as recalled from cur_month name but it returns 31!!
    Please Login or Register  to view this content.
    I will then use the result multiply it by 24 to calculate the total number of hours in this month then calculate percentage required
    Is there any simple way to do this?!
    Reply With QuoteRegister To Reply
  2. 05-07-2023, 11:38 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Calculate total number of hours in current month

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 05-07-2023, 11:43 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Calculate total number of hours in current month

    Maybe it's as simple as:

    =DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(1&cur_month),1),0))
    Reply With QuoteRegister To Reply
  4. 05-07-2023, 12:06 PM #4
    Shalaby
    Shalaby is offline
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Re: Calculate total number of hours in current month

    Quote Originally Posted by Glenn Kennedy View Post
    Maybe it's as simple as:

    =DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(1&cur_month),1),0))
    Cool, but if multiplied by 24 it will give 720 as a number, I want to format the cell so the result give =720:00 as [hr]:mm
    Reply With QuoteRegister To Reply
  5. 05-07-2023, 12:10 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Calculate total number of hours in current month

    So... format the cell as [h]:mm
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 05-07-2023, 12:11 PM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Calculate total number of hours in current month

    I used a reference to Text in A1, rather than your named range, in the above file. But the same should apply in your sheet. If not, please supply that sample sheet I asked for back at Post 2.
    Reply With QuoteRegister To Reply
  7. 05-07-2023, 12:15 PM #7
    Shalaby
    Shalaby is offline
    Registered User
    Join Date
    11-10-2014
    Location
    Egypt
    MS-Off Ver
    Excel 360
    Posts
    19

    Re: Calculate total number of hours in current month

    Ok Done, thanks, I thought I should multiply by 24 to get the total number of hrs, but your formula already calculates the number of hrs correctly!
    Thanks again
    Reply With QuoteRegister To Reply
  8. 05-07-2023, 12:18 PM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Calculate total number of hours in current month

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate number of workdays from total hours
    By Iain170 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-16-2023, 03:26 AM
  2. Calculate number of people needed based on total hours and number machines avalable
    By pmw19800 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2019, 05:09 AM
  3. Calculate Number of hours for a particualar month
    By petford in forum Excel General
    Replies: 11
    Last Post: 09-08-2017, 09:55 AM
  4. total of days within current month with dates from before the current month and year
    By ozinal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-29-2016, 01:28 PM
  5. Write a total number of hours each month into appropriate cell
    By koli99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2014, 02:49 AM
  6. Trying to add total hours worked in current month
    By Useless_w/_excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-23-2009, 01:17 AM
  7. [SOLVED] how do i display the total number of days in the current month in.
    By timerigger in forum Excel General
    Replies: 6
    Last Post: 03-20-2005, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1