Using Excel to Create Timesheets (but dumb ones)


Recommended Posts

Someone in my team did the old "use excel because of the boxes" thing years ago and created timesheets using an excel spreadsheet.

These spreadsheets don't do any calculations.

 

I was looking at the IF/THEN command and whether it could have more than one IF?

For example if box A1 says W then box Z1 says WORK, but I also need it to check if box A1 has another letter and if it does, put something else in box Z1 - there may be 5 or 6 potential letters in box A1 and each one would need to print something different in Z1.

 

Is that possible?

 

Not too worried about calculating the hours worked as yet a that should be easy (ish!).

Prob be easier to use a vlookup table.  You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1.

 

This might help

https://exceljet.net/excel-functions/excel-vlookup-function

  • Like 3
  On 03/10/2020 at 15:07, BudMan said:

Prob be easier to use a vlookup table.  You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1.

 

This might help

https://exceljet.net/excel-functions/excel-vlookup-function

Expand  

Could also use a 3D array lookup using INDEX / MATCH as well, though this could be costly.

Hi

Thanks - have used this function instead and it works fine. Put the codes in a different sheet and added a query to be able to call the codes / display text from that sheet.

 

The next part is hours worked, which seems to involve a long IF equation otherwise it shows minus hours:

 

=IF(E15>C15, E15-C15, 1-C15+E15)

 

Which gives me the result I'm looking for. 

 

I may be back depending on how complicated I want to make it but managed to also add protection for the formulas, so I can't change those cells, while still being able to add hours worked and such. 

 

Cheers

Edited by Sir Topham Hatt
  • 5 weeks later...

Okay, so here we go.

 

Can I have two IF commands in one cell?

 

For example, I have the following in one cell:

 

=IF(E18 > C18,E18 - C18,0 - C18+E18)

 

which gives me the total hours worked, depending on what I write in the previous columns.

 

Elsewhere on the same sheet, I have a column for "codes".  If I type in W, the next column prints "Working", or OT prints "Overtime".  If I write "FH", it'll say "From Home" but the hours are still 0.

Can I change the above IF command to also look at the "Codes" column and if it says a specific code ("FH"), then it'll automatically change the hours to a number I set?

 

I know this is straying away from dumb timesheets but just wondering.

 

Thanks

  On 03/10/2020 at 15:07, BudMan said:

Prob be easier to use a vlookup table.  You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1.

 

This might help

https://exceljet.net/excel-functions/excel-vlookup-function/worktime

Expand  

Thank You for guide
But i recommend to add a HLOOKUP guide too for timesheets building
 

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Google's financial performance remains unaffected by competition from ChatGPT by Pradeep Viswanathan Today, Alphabet (Google's parent company) announced earnings for the quarter ended June 30, 2025. Overall revenue was $96.4 billion, up 14% YoY, thanks to the strong performance of Google Search & other, YouTube ads, Google subscriptions, platforms, and devices, and Google Cloud. Each of these businesses delivered double-digit growth in Q2. Google Cloud posted the strongest growth at 32% YoY, driven by the growing momentum of Google Cloud Platform (GCP), which includes AI Infrastructure and Generative AI Solutions. Its annual revenue run-rate is now more than $50 billion. In addition to revenue growth, Alphabet also posted strong margin improvement, reporting an operating income of $31.2 billion, up 32% YoY. Sundar Pichai, Alphabet CEO, said: During the earnings call, Sundar Pichai also revealed that the Gemini App now has more than 450 million monthly active users and that daily requests grew by over 50% since the last quarter. Despite ChatGPT’s explosive growth, Google appears unaffected so far, thanks to the strong financial performance of Google Search and the growing momentum of Gemini. However, it remains to be seen whether Google can hold its ground as ChatGPT increasingly attracts younger users who are turning to it over traditional Google searches.
    • Here is a better view 
    • I care more about ethical LLMs than open source LLMs. Would be great if they declare that the training data was sourced without copyright infringement.
    • Yep social media addiction is no different than alcohol or cigarettes.
    • I strongly feel it is a test bed for foldables. No other reason to slim down the phone even further, despite have a massive camera bump.
  • Recent Achievements

    • Collaborator
      fernan99 earned a badge
      Collaborator
    • Collaborator
      MikeK13 earned a badge
      Collaborator
    • One Month Later
      Alexander 001 earned a badge
      One Month Later
    • One Month Later
      Antonio Barboza earned a badge
      One Month Later
    • Week One Done
      Antonio Barboza earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      590
    2. 2
      ATLien_0
      225
    3. 3
      Michael Scrip
      167
    4. 4
      Xenon
      139
    5. 5
      +FloatingFatMan
      128
  • Tell a friend

    Love Neowin? Tell a friend!