+ Reply to Thread
Results 1 to 12 of 12

Formula for hours worked in a day, for weekdays and weekends

  1. 03-30-2016, 07:35 AM #1
    crisancomis
    crisancomis is offline
    Registered User
    Join Date
    03-30-2016
    Location
    Romania
    MS-Off Ver
    2010
    Posts
    7

    Formula for hours worked in a day, for weekdays and weekends

    hello all, im pretty new to excel, and i really need help

    I would like a formula for hours worked in a day. As in E16 (the days column) is a value from Monday to Friday, E17(the hours worked in a day column) should be 8, and if its from Saturday to Sunday it should be x (no days worked at all).

    The thing is the days column is the result of another formula, and I'm wondering the formula to calculate the hours could even work. As in can it see the days so the formula works properly?


    also is there any way to make the result of a formula be displayed UPPERCASE?

    please advise thank you
    Last edited by crisancomis; 03-30-2016 at 07:42 AM.
    Reply With QuoteRegister To Reply
  2. 03-30-2016, 07:54 AM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula for hours worked in a day, for weekdays and weekends

    Quote Originally Posted by crisancomis View Post
    also is there any way to make the result of a formula be displayed UPPERCASE?
    Like this...

    =UPPER(your formula here)

    For your other question...

    I think we'll need to see some sample data that shows us what result you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 03-30-2016, 08:11 AM #3
    pjwhitfield
    pjwhitfield is offline
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula for hours worked in a day, for weekdays and weekends

    Can you upload your workbook?

    and yes to the Uppercase, simply wrap the existing formula in =UPPER()
    If someone has helped you then please add to their Reputation
    Reply With QuoteRegister To Reply
  4. 03-30-2016, 09:41 AM #4
    crisancomis
    crisancomis is offline
    Registered User
    Join Date
    03-30-2016
    Location
    Romania
    MS-Off Ver
    2010
    Posts
    7

    Re: Formula for hours worked in a day, for weekdays and weekends

    Quote Originally Posted by pjwhitfield View Post
    Can you upload your workbook?

    and yes to the Uppercase, simply wrap the existing formula in =UPPER()
    i uploaded it to my google drive, its in my native language hope that's ok, the grey areas are Saturdays and Sundays, its filled out by hand, but id like a formula for the future.

    no I mean its cell n11 =E16, how do I edit it so =E16 is uppercase? I mean =UPPER(=E16) didn't work
    Reply With QuoteRegister To Reply
  5. 03-30-2016, 09:42 AM #5
    crisancomis
    crisancomis is offline
    Registered User
    Join Date
    03-30-2016
    Location
    Romania
    MS-Off Ver
    2010
    Posts
    7

    Re: Formula for hours worked in a day, for weekdays and weekends

    Quote Originally Posted by pjwhitfield View Post
    Can you upload your workbook?

    and yes to the Uppercase, simply wrap the existing formula in =UPPER()
    =UPPER(=E16) didn't work
    Reply With QuoteRegister To Reply
  6. 03-30-2016, 09:45 AM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula for hours worked in a day, for weekdays and weekends

    Like this...

    =UPPER(E16)
    Reply With QuoteRegister To Reply
  7. 03-30-2016, 09:49 AM #7
    crisancomis
    crisancomis is offline
    Registered User
    Join Date
    03-30-2016
    Location
    Romania
    MS-Off Ver
    2010
    Posts
    7

    Re: Formula for hours worked in a day, for weekdays and weekends

    Quote Originally Posted by Tony Valko View Post
    Like this...

    =UPPER(E16)
    oddly that just turns the text into random numbers

    here is the filled out work sheet if that helps i uploaded it to my google drive
    Reply With QuoteRegister To Reply
  8. 03-30-2016, 09:55 AM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula for hours worked in a day, for weekdays and weekends

    I have no experience with Google spreadsheets.

    What's in E16?
    Reply With QuoteRegister To Reply
  9. 03-30-2016, 10:01 AM #9
    Debraj Roy
    Debraj Roy is offline
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Formula for hours worked in a day, for weekdays and weekends

    Try ..
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...
    Reply With QuoteRegister To Reply
  10. 03-30-2016, 10:04 AM #10
    Phuocam
    Phuocam is offline
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,910

    Re: Formula for hours worked in a day, for weekdays and weekends

    Try this in E18:
    =IF(WEEKDAY(E$16,2)>5,"X",8)
    Copy across and down
    Reply With QuoteRegister To Reply
  11. 03-30-2016, 10:07 AM #11
    crisancomis
    crisancomis is offline
    Registered User
    Join Date
    03-30-2016
    Location
    Romania
    MS-Off Ver
    2010
    Posts
    7

    Re: Formula for hours worked in a day, for weekdays and weekends

    Quote Originally Posted by Phuocam View Post
    Try this in E18:
    =IF(WEEKDAY(E$16,2)>5,"X",8)
    Copy across and down
    it says error
    Reply With QuoteRegister To Reply
  12. 03-30-2016, 10:18 AM #12
    crisancomis
    crisancomis is offline
    Registered User
    Join Date
    03-30-2016
    Location
    Romania
    MS-Off Ver
    2010
    Posts
    7

    Re: Formula for hours worked in a day, for weekdays and weekends

    Quote Originally Posted by Debraj Roy View Post
    Try ..
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    it says error
    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 total hours worked between 2 date/time stamps ignoring weekends and holidays
    By drewship in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-01-2015, 09:59 AM
  2. Time during business hours (week days) and time during Out of Hours (weekdays+weekends)
    By wintheranders in forum Excel General
    Replies: 2
    Last Post: 11-12-2014, 11:56 AM
  3. Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours
    By nikkiw in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  4. Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours
    By nikkiw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2013, 02:26 PM
  5. [SOLVED] Macro/Formula to skip or omit values from weekends to just weekdays.
    By escapes88 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-20-2013, 08:00 AM
  6. [SOLVED] Punch-Time Data. Calculating # of hours worked and # of hours worked alone per shift.
    By Daga3 in forum Excel General
    Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  7. Calculate Hours Worked, Minus Weekends
    By Violator in forum Excel General
    Replies: 13
    Last Post: 11-15-2009, 05:02 AM

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