Thanks for the useful video! Useful tip for others who want to count the days between dates: instead of using "dd" to output in numbers, use "00" or "000". Only realized after looking more closely that the value output using "dd" is only accurate for differences less than 1 month apart.
Hi Alan, You can put the differences into two columns using an IF to determine whether the answer is negative and reversing the maths if so for the negative column. (Similar to double-entry bookkeeping with credits and debits.) This then negates the need for TEXT and ABS and allows maths on the resultant numbers (if needed). I like your method though if no need of further maths...
Thank you for this, this works perfectly for my scenario when reporting staff hours..."scheduled hours vs. hours worked" and whether they were over/under their scheduled shift.
Hi Alan Good tutorial. I am the guy who sent you the original question and your solution helped on similar scenarios I was doing. Great explanation and keep up the good work. Cheers
Hi Alan. An interesting problem. Since the underlying fraction of day is still calculated (just the formatting is wonky), another method would be to figure out the number of seconds difference with something like: =(C3-C2)*(24*60*60) which would show positive/negative seconds. You could then convert the number of positive/negative seconds into pos/neg minutes and seconds in adjacent columns. But, to get the time formatting, there seems to be no other method than converting to text, as you demonstrated, outside of the 1904 date change which could be risky if the workbook includes other date/time related inputs. Thanks for this discussion and the heads up on how it works :)) Thumbs up!!
Another possible approach is to show the calculation converted to doubles. Then convert back in a second column for display purposes using your text formatting method. Then you would always have data for possible onward calculation. This is simply exposing what Excel does behind the scenes, but useful for reminding us how dates and times work.
Thanks for the video, it helped me a lot. For the second approach you dont need the abs() function you just could have switched C and D in the calculation to get the positive difference and format it as text with the minus afterwards :)
I have this problem, and I have hundreds of dates. I tried to use the 1904 date system and make adjustments however when I import new dates, I need to do it again so that is not an option. The text feature will not work either as I need to use that field to perform additional calculations. I posted this problem in a forum and was asked why not use the ABS feature to display the absolute value. That is not a bad idea, however I would need a way to identify which fields were originally negative so I can address that in the formula. Do you have any other ways or ideas to display negative times that will allow me to do calculations or a way to use ABS but identify the negative ones with a color or something?
Is there any logical reason why Excel cannot handle negative times properly? No problem with Libre Office or Google sheets. 1904 is no solution because it messes up every date on the sheet, text is no option because it's no good for calculations.
Hi! just a question can you show us how to deduct hours on any time for example 1 want to deduct 2 hours from 12:00am or 1am hope you can helps us Thank you! more power
Thanks for the useful video!
Useful tip for others who want to count the days between dates:
instead of using "dd" to output in numbers, use "00" or "000". Only realized after looking more closely that the value output using "dd" is only accurate for differences less than 1 month apart.
Fantastic. Many Thanks for the Useful Video
You are welcome
Thank you! I really appreciate it!
No problem!
Thanq I like the way of explain the solution
Thank you very much 🙂
Hi Alan,
You can put the differences into two columns using an IF to determine whether the answer is negative and reversing the maths if so for the negative column. (Similar to double-entry bookkeeping with credits and debits.) This then negates the need for TEXT and ABS and allows maths on the resultant numbers (if needed). I like your method though if no need of further maths...
Thank you for this, this works perfectly for my scenario when reporting staff hours..."scheduled hours vs. hours worked" and whether they were over/under their scheduled shift.
You're welcome. Good to hear.
Very helpful
Glad to hear that 👍🏼
Great, one of the best and easiest way to put the logic in the present scenario... 😘😀
Thank you! 😃
Hi Alan
Good tutorial.
I am the guy who sent you the original question and your solution helped on similar scenarios I was doing. Great explanation and keep up the good work. Cheers
Hey Dave, thanks for the question and the comments 👍
Thank You So Much for this sir, I am really really thankful to you....
You're welcome, Meraj.
Great, thank you so much
You are welcome!
Hi Alan. An interesting problem. Since the underlying fraction of day is still calculated (just the formatting is wonky), another method would be to figure out the number of seconds difference with something like: =(C3-C2)*(24*60*60) which would show positive/negative seconds. You could then convert the number of positive/negative seconds into pos/neg minutes and seconds in adjacent columns. But, to get the time formatting, there seems to be no other method than converting to text, as you demonstrated, outside of the 1904 date change which could be risky if the workbook includes other date/time related inputs. Thanks for this discussion and the heads up on how it works :)) Thumbs up!!
Thanks, Wayne.
Another possible approach is to show the calculation converted to doubles. Then convert back in a second column for display purposes using your text formatting method. Then you would always have data for possible onward calculation.
This is simply exposing what Excel does behind the scenes, but useful for reminding us how dates and times work.
Thanks it really help my computation.. 😊
Most welcome 😊
Thank you soo much for this
No problem 😊
Thanks for the video, it helped me a lot.
For the second approach you dont need the abs() function you just could have switched C and D in the calculation to get the positive difference and format it as text with the minus afterwards :)
Thank you
I have this problem, and I have hundreds of dates. I tried to use the 1904 date system and make adjustments however when I import new dates, I need to do it again so that is not an option. The text feature will not work either as I need to use that field to perform additional calculations. I posted this problem in a forum and was asked why not use the ABS feature to display the absolute value. That is not a bad idea, however I would need a way to identify which fields were originally negative so I can address that in the formula. Do you have any other ways or ideas to display negative times that will allow me to do calculations or a way to use ABS but identify the negative ones with a color or something?
Good work
Thank you so much 😀
@@Computergaga You're welcome
Is there any logical reason why Excel cannot handle negative times properly? No problem with Libre Office or Google sheets.
1904 is no solution because it messes up every date on the sheet, text is no option because it's no good for calculations.
Hi! just a question can you show us how to deduct hours on any time for example 1 want to deduct 2 hours from 12:00am or 1am
hope you can helps us Thank you! more power
Sure. You will need the date in a column or the same cell to subtract a time from 1 am to the previous day accurately.
@@Computergaga ill look forward in you teaching us this with a video! god bless i kinda not super good with excel hahaha
I have a problem. I want hh:mm because my data have high values
Sure. That shouldn't be a problem. Use [h]:mm if its over 24 hours.
Great. Copied
You're welcome 👍