Calendar: Calculate Taken vs Planned time

**Smom** · 12-13-2011, 11:58 AM

PTO Calendar.xlsxHello.

If you look at the attachment, you will see that my spreadsheet is a calendar. The goal is for the User to add their "earned time off" in cell D2, then use the calendar portion of the sheet to plan their time off. As they enter hours in the cell dates, I would like the spreadsheet to perform the following functions:[INDENT] Cell D3: Total up those hours that are previous to the date the user is accessing the sheet. (So, if I had an 8 in 05Jan /cell C11/, and I opened the spreadsheet on 06Jan, those 8 hours would be listed as "Taken PTO". [INDENT] Cell D4: Total up those hours that follow the date the user is accessing the sheet: If I opened it today and the 8 hours were listed in cell C11, the "Taken PTO" would hold "0", and "Planned PTO" would indicate 8.

Right now, I'm using "P" to indicated planned hours - and the sheet is counting that as 8 hours. I'm wanting to be able to 'plan' for less than 8 hours if needed. Is there a simple solution for this? I've dug through many of the postings here, and am not finding exactly what I'm looking for, and not excel-saavy enough to figure it out on my own!

Thank you!

**rscsmith** · 12-18-2011, 11:15 PM

Hi Smom

The formula in cell C38 should read:

Please Login or Register  to view this content.

Cell C39 should read:

Please Login or Register  to view this content.

See attached file.

I have also made some other changes. Cell B6 is the year of the sheet. It feeds in to the date values that appear on the sheet. This allows you to simply replace 2011 with 2012, and all the correct dates appear.

Note - on your original, Feb-29 was the only date in 2012. All the others were in 2011.

Conditional formatting determines the weekends and colours them green.

Hope this helps. Cheers, Rob.

**arthurbr** · 12-19-2011, 03:51 AM

Hi and welcome to the board

Providing an example sheet usually provides more answers than a picture.

@ rcsmith : why is the NOW() function necessary ? If I understand correctly the OP is dealing with dates only, not dates and time

**Smom** · 12-20-2011, 11:24 AM

ROB: It helps tremendously, and actually solves the problem for me. I had not realized the dates were 2011, duh. But, even at that, I couldn't have come up with the formula. Very much appreciate your help! Now I'm going to try to get the conditional formatting to work as in your document. I manually highlighted the weekends in mine - very time consuming!! Thanks again for your help!

ARTHURBR: I attached both a picture, and an actual spreadsheet. For some reason it attached before "Hello" in my message above. (I attached the spreadsheet after original submission.) Anyways, Rob's solution worked, all is well. Loving this forum! :c)

**rscsmith** · 12-20-2011, 07:22 PM

Hi Smom

Let me know if you can't get your conditional formatting working.

Also, please remember to change your thread title to [SOLVED] (press Go Advanced below).

And, if you felt like adding to my reputation, click the star at the bottom of this post. :D

Cheers, Rob.

**Smom** · 12-20-2011, 09:41 PM

I was able to figure it out. I learned alot from that one little post. Thanks so much, Rob! I'd give you multiple stars if able!

**Smom** · 12-26-2011, 11:08 AM

Trying to update the subject to indicate SOLVED!

Calendar: Calculate Taken vs Planned time

LinkBack

Thread Tools

Rate This Thread

Display

Calendar: Calculate Taken vs Planned time

Re: Calendar: Calculate Taken vs Planned time

Re: Calendar: Calculate Taken vs Planned time

Re: Calendar: Calculate Taken vs Planned time

Re: Calendar: Calculate Taken vs Planned time

Re: Calendar: Calculate Taken vs Planned time [SOLVED]

Re:[SOLVED] Calendar: Calculate Taken vs Planned time

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions