Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-27-2023, 09:19 PM
Karen615 Karen615 is offline Adding Time over Two Days Plus Additional Info Windows 11 Adding Time over Two Days Plus Additional Info Office 2021
Competent Performer
Adding Time over Two Days Plus Additional Info
 
Join Date: Jun 2011
Location: Chicago
Posts: 109
Karen615 is on a distinguished road
Smile Adding Time over Two Days Plus Additional Info

I don't know if this is possible. I can only hope.



In my attached time sheet, time is entered as 12-hour time and converted to 24-hour time. Then time is totaled (as an example) in column F. If the employee is off or on vacation, they enter OFF or VAC in the first cell of the day. Now I cannot add a formula to sum up the time because of these instances. Is there a way to sum the time IF time is entered. Then if any of these codes (BG, CHI, MTP, NB, OFF, T/0, VAC) are entered in the first cell of the day, I would like it to return 0 in the total column. Another issue I have is, the times may be from 11:00 PM on one day to 7:00 AM the next day.

This may be a long shot, but is this at all possible? PLEASE HELP!

Thank you in advance for your help.
Karen
Attached Files
File Type: xlsx Template.xlsx (29.6 KB, 4 views)
Reply With Quote
  #2  
Old 11-28-2023, 01:10 AM
ArviLaanemets ArviLaanemets is offline Adding Time over Two Days Plus Additional Info Windows 8 Adding Time over Two Days Plus Additional Info Office 2016
Expert
 
Join Date: May 2017
Posts: 857
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

As about calculating totals, it is easy - use SUMIFS()! You add the time in row either when code cell is empty, or the code cell equals your single valid value for it, or OR(CodeCell=ValidValue1, CodeCell=ValidValue2, ...) = True, or OR(CodeCell<>"VASC", CodeCell<>"OFF",...) = True (using last 2 depending which has less values to compare).

About times. As it follows from your post, your time intervals can be over midnight. It is possible to calculate the length of time intervals for such cases using time value, but it means you always have to make an additional comparision to check, has the start time bigger value as end time (and this works only, when there never will be time intervals over 24 hours). Much easier (and much safer) way will be to enter start and end time values as datetime ones (like 28.11.2023 10:00). In case this is really a NO for you, the another option is to have calculated columns for StartDateTime and EndDataTime, and use them in your calculations.

About time formats you use - unless you enter times as strings, it doesn't count. The format used to display date or time don't change the value, it only changes how you will see it!
Reply With Quote
  #3  
Old 11-28-2023, 01:33 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Adding Time over Two Days Plus Additional Info Windows 10 Adding Time over Two Days Plus Additional Info Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,747
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Something like the attached?
I changed the value of E9 to show an example of overnight hours.
This being said, your extensive use of merged cells will get you in trouble when you will want to analyze further data. Merged cells should be avoided at all costs.


Also, if I understand part of your problem, when summing a range, XL does not care about text
Attached Files
File Type: xlsx Template.xlsx (29.3 KB, 19 views)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 11-28-2023, 07:50 PM
Karen615 Karen615 is offline Adding Time over Two Days Plus Additional Info Windows 11 Adding Time over Two Days Plus Additional Info Office 2021
Competent Performer
Adding Time over Two Days Plus Additional Info
 
Join Date: Jun 2011
Location: Chicago
Posts: 109
Karen615 is on a distinguished road
Default

@Pecoflyer

I very much appreciate your help. Great solution!

If it's not too much trouble, would you kindly explain the formula to me?
I know why you used ISNUMBER and the ranges, of course, but not sure of the rest.

Thank you again for your kindness.

Karen
Reply With Quote
  #5  
Old 11-29-2023, 12:52 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Adding Time over Two Days Plus Additional Info Windows 10 Adding Time over Two Days Plus Additional Info Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,747
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Perhaps this will help Calculate number of hours between two times - Excel formula | Exceljet
Don't forget to mark the thread as " solves " ( see thread tools)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #6  
Old 11-29-2023, 12:11 PM
Karen615 Karen615 is offline Adding Time over Two Days Plus Additional Info Windows 11 Adding Time over Two Days Plus Additional Info Office 2021
Competent Performer
Adding Time over Two Days Plus Additional Info
 
Join Date: Jun 2011
Location: Chicago
Posts: 109
Karen615 is on a distinguished road
Default

@ArviLaanemets

Thank you so much for all the great suggestions.

These functions are new to me and I will have to research them. Iím always up for learning anything new about Excel.

Thank you,
Karen
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Power query - adding additional column agreeff Excel 1 02-13-2023 01:13 AM
Adding Time over Two Days Plus Additional Info Timesheets to Invoices - Time being imported as days dave6084 Mail Merge 1 11-02-2019 11:01 PM
Adding Time over Two Days Plus Additional Info adding days to a date euterpia Excel 1 01-18-2016 07:42 AM
Calculating Average Time across days lizakay Excel 3 11-25-2015 05:16 PM
Adding additional job/education/reference category boxes in resume template? Audible Nectar Word 1 11-16-2012 01:16 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:03 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft