Time and Date Sum w/ auto filter

N

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
395
Office Version
  1. 2016
Platform
  1. Windows
I have a vacation tracker for work, that always me to see when people start and return from vacation or other time off situations. The issue that I am having is two parts. Main one: I have figured out how to calculate the days between days by using b2-a1, but I have to incorporate the times. So if they start at 0900 then that is a day used but if they start at 1800 that day is not used. This makes a world of difference. Below is my visual example.

The cells in with the "V" are the ones counted just for visual reasons. This is not how the sheet actually looks.

[TABLE="width: 561"]
<colgroup><col style="width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:2937; width:65pt" width="87" span="2"> <col style="mso-width-source:userset;mso-width-alt:3651;width:81pt" width="108"> <col style="width:50pt" width="67" span="6"> </colgroup><tbody>[TR]
[TD="width: 154, colspan: 2"]Left before the worday started, this day counts[/TD]
[TD="width: 87"][/TD]
[TD="width: 108"][/TD]
[TD="width: 67"][/TD]
[TD="width: 67"][/TD]
[TD="width: 67"][/TD]
[TD="width: 134, colspan: 2"]Returned after the work day , this day is counted[/TD]
[TD="width: 67"][/TD]
[/TR]
[TR]
[TD="align: right"]900[/TD]
[TD="class: xl66, align: right"]V 24-Aug-18
[/TD]
[TD="class: xl65"]V 25[/TD]
[TD="class: xl65"]V 26[/TD]
[TD="class: xl65"]V 27[/TD]
[TD="class: xl65"]V 28[/TD]
[TD="class: xl65"]V 29[/TD]
[TD="class: xl65, align: right"]1800[/TD]
[TD="class: xl66, align: right"]V 30-Aug-18
[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Left after the work day, this day does not count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Returned before the word day started, day not counted[/TD]
[/TR]
[TR]
[TD="align: right"]1800[/TD]
[TD="class: xl67, align: right"] V 24-Aug-18
[/TD]
[TD="class: xl65"]V 25[/TD]
[TD="class: xl65"]V 26[/TD]
[TD="class: xl65"]V 27[/TD]
[TD="class: xl65"]V 28[/TD]
[TD="class: xl65"]V 29[/TD]
[TD="class: xl68, align: right"]900[/TD]
[TD="class: xl69, align: right"]30-Aug-18[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Left before the work day started, this day counts
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Returned before the work day started, day not counted[/TD]
[/TR]
[TR]
[TD="align: right"]900
[/TD]
[TD="class: xl66, align: right"]V 24-Aug-18
[/TD]
[TD="class: xl65"]V 25[/TD]
[TD="class: xl65"]V 26[/TD]
[TD="class: xl65"]V 27[/TD]
[TD="class: xl65"]V 28[/TD]
[TD="class: xl65"]V 29[/TD]
[TD="class: xl68, align: right"]900[/TD]
[TD="class: xl69, align: right"]30-Aug-18[/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Left after the work day, this day is not counted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Returned After the work day, this day is counted[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68, align: right"]1800[/TD]
[TD="class: xl69, align: right"]24-Aug-18[/TD]
[TD="class: xl65"]V 25[/TD]
[TD="class: xl65"]V 26[/TD]
[TD="class: xl65"]V 27[/TD]
[TD="class: xl65"]V 28[/TD]
[TD="class: xl65"]V 29[/TD]
[TD="class: xl65, align: right"]1800[/TD]
[TD="class: xl66, align: right"]V 30-Aug-18
[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { border: 0.5pt solid windowtext; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl66 { border: 0.5pt solid windowtext; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl67 { }.xl68 { border: 0.5pt solid windowtext; background: white none repeat scroll 0% 0%; }.xl69 { border: 0.5pt solid windowtext; background: white none repeat scroll 0% 0%; }</style>





Part two as well as what the section of the sheet looks like is, I would like for it to auto shuffle the rows according to who comes back the earliest. There for it makes it easier to delete them from the bottom of the list. If it could be a macro button that would work out if possible



[TABLE="width: 361"]
<tbody>[TR]
[TD="class: xl65, width: 87"]time[/TD]
[TD="class: xl65, width: 87"]date[/TD]
[TD="class: xl65, width: 108"]time[/TD]
[TD="class: xl65, width: 67"]date[/TD]
[TD="class: xl65, width: 67"]number of days[/TD]
[TD="class: xl65, width: 67"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]900[/TD]
[TD="class: xl66, align: right"]24-Aug-18[/TD]
[TD="class: xl65, align: right"]1800[/TD]
[TD="class: xl67, align: right"]30-Aug-18[/TD]
[TD="class: xl65"]formula[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1800[/TD]
[TD="class: xl66, align: right"]24-Aug-18[/TD]
[TD="class: xl65, align: right"]900[/TD]
[TD="class: xl67, align: right"]31-Aug-18[/TD]
[TD="class: xl65"]formula[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]900[/TD]
[TD="class: xl66, align: right"]24-Aug-18[/TD]
[TD="class: xl65, align: right"]900[/TD]
[TD="class: xl67, align: right"]1-Sep-18[/TD]
[TD="class: xl65"]formula[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1800[/TD]
[TD="class: xl66, align: right"]24-Aug-18[/TD]
[TD="class: xl65, align: right"]1800[/TD]
[TD="class: xl67, align: right"]2-Sep-18[/TD]
[TD="class: xl65"]formula[/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { border: 0.5pt solid windowtext; }.xl66 { border: 0.5pt solid windowtext; }.xl67 { border: 0.5pt solid windowtext; background: rgb(255, 192, 0) none repeat scroll 0% 0%; }</style>

I truly hope this is not as confusing or complicated as i am making it. Thank you to any one that can help.
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top